Oracle DBA: TableSpace Management

TABLE SPACE MANAGEMENT

Tablespaces are logical entities—each of an application’s tables and indexes are stored as a segment, and the segments are stored in the datafiles that are parts of tablespaces. A tablespace is thus a logical allocation of space for Oracle schema objects.

Every Oracle tablespace must have the mandatory System and Sysaux tablespaces. The System tablespace is permanent and contains vital data dictionary information that helps the database function. The System tablespace is the first tablespace you create when you create a new database. The Sysaux tablespace is auxiliary to the System tablespace, and it stores the metadata for various Oracle applications, as well as operational data for internal performance tools like the Automatic Workload Repository. Both the System and Sysaux tablespaces are treated differently from the other tablespaces. You can’t rename or drop either of these tablespaces.

When you use the word tablespace, you’re actually referring to a permanent tablespace, which is where you store your schema objects. (If you’re migrating from a pre–Oracle Database 10g release database, you must first create the Sysaux tablespace before upgrading.) All permanent tablespaces are created by using Oracle datafiles. In addition to permanent tablespaces, you have the following important types of Oracle tablespaces:

• If a tablespace is temporary, the tablespace itself is permanent; only the segments saved in

the tablespace are temporary. A temporary tablespace can be used for sorting operations and for tables that exist only for the duration of the user’s session. Dedicating a tablespace for these kinds of operations helps to reduce the I/O contention between temporary segments and permanent segments stored in another tablespace, such as tables.

• Undo tablespaces are a type of permanent tablespace that are used to store undo data, which is used to undo changes to data.

Tablespaces can be either dictionary managed or locally managed. In a dictionary-managed

tablespace, extent management is recorded in data dictionary tables. Therefore, even if all

application tables are in the USERS tablespace, the SYSTEM tablespace will still be accessed for

managing DML on application tables. Because all users and applications must use the SYSTEM

tablespace for extent management, this creates a potential bottleneck for write-intensive applications.

In a locally managed tablespace, Oracle maintains a bitmap in each datafile of the tablespace to

track space availability. Only quotas are managed in the data dictionary, dramatically reducing

the contention for data dictionary tables.

As of Oracle9i, if the SYSTEM tablespace is locally managed, then all other tablespaces must

be locally managed if both read and write operations are to be performed on them. Dictionary managed tablespaces must be read-only in databases with a locally managed SYSTEM tablespace.

· Before you can create tables or indexes, you should create the tablespaces to hold the data. Tablespaces consist of one or more datafiles (or tempfiles, if you are creating a temporary tablespace). Oracle tablespaces makes it easy for you to group related information. A datafile can be a part of one and only one tablespace.

Using Oracle Managed Files (OMF) can make tablespace datafile management even easier.

With OMF, the DBA specifies one or more locations in the file system where datafiles, control

files, and redo log files will reside, and Oracle automatically handles the naming and management

of these files.

Tablespace Extent Sizing and Space Management

Before you actually create a tablespace, you must be aware of two other important concepts: extent sizing and segment space management.

Allocating the Extent Size: Autoallocate vs. Uniform

Any time an Oracle object needs to grow in size, space is added to the object in terms of extents. When you create locally managed tablespaces, you have two options for managing the extent sizes:

· you can let the database automatically choose the extent size (by selecting the AUTOALLOCATE option) or

· you can specify that the tablespace be managed with uniform-sized extents (the UNIFORM option).

· If you choose the UNIFORM option, you specify the actual size of the extents by using the SIZE clause.

· If you omit the SIZE clause, Oracle will create all extents with a uniform size of 1MB, but you can choose a much larger uniform extent size if you wish.

· You can’t change the extent size once you create the tablespace.

· If you think that all the segments in a tablespace are approximately of the same size, and that they’ll grow in a similar fashion, you can choose the UNIFORM extent size option.

since extent size has a bearing on the read and write performance of a segment. For example, if you choose a very small UNIFORM extent size, the database can’t prefetch data or do multiblock reads, thus adversely impacting performance. Oracle suggests the following extent size guidelines, if you wish to set the extent sizes yourself:

• 64KB for small segments

• 1MB or medium segments

• 64MB for large segments

Under the AUTOALLOCATE option, Oracle will manage the extent size automatically. The extent size starts at 64KB and is progressively increased to 64MB by the database. The database automatically decides what size the new extent for an object should be, based on the segment’s growth pattern. Interestingly, Oracle will increase the extent size for an object automatically as the object grows! Autoallocate is especially useful if you aren’t sure about the growth rate of an object and you would like Oracle to deal with it.

Automatic vs. Manual Segment Space Management

You can use the space in an Oracle block for two purposes: Inserting fresh data or updating existing data in the blocks. When you delete data from a block, or an update statement changes the existing data length to a smaller size, there will be an increase in free space in the block. Segment space management is how Oracle manages this free space within the individual Oracle data blocks.

If you specify manual segment space management (by using the keyword MANUAL), the database manages the free space of segments in the tablespace using entities known as freelists and a pair of storage parameters, PCTFREE and PCTUSED.

The PCTFREE parameter lets you reserve a percentage of space in each data block for future updates to existing data. For example, you may have some data on a person’s address in a certain block. If you update that address later, so that it is larger, there should ideally be room in the existing block for the enlarged address. (updating the records eg. previously name John after that we will update the name as John Smith). If mention PCTFREE as 10, oracle will adding the new rows to block up to 90% it allows 10% for future updates. This is exactly what the PCTFREE parameter provides: room for the existing rows to grow.

The PCTUSED parameter, on the other hand, deals with the threshold below which the used space must fall before new data can be placed in the blocks. If the PCT USED was set to 60 this means if the data inside the block is 60 it is FULL and if the data inside the block is 59 it is Empty. Oracle can’t insert new data into the block until the amount of used space falls below this threshold level. This is the parameter which specify in percent that a block can only used for insert or come in the free list(list of blocks in segment ready for insert operation) when used space in a block is less than PCTUSED.

Suppose value of PCTUSED is 40 and PCTFREE is 20 then data can be inserted till 80 of the block directly. And suppose the used space is 60 and some one has perform a delete operation in a row in the same block which brings the used space to 50 .Now one cannot insert any record in the same block unless the used space comes down below 40 i.e. PCTUSED.

MIGRATING THE TABLESPACES FROM DICTIONARY MANAGED TO LOCALLY MANAGED TABLESPACES

SQL> EXECUTE dbms_space_admin.tablespace_migrate_to_local ('USERS');

MIGRATING THE TABLESPACES FROM LOCALLY MANAGED TO DICTIONARY MANAGED TABLESPACES

SQL> EXECUTE dbms_space_admin.tablespace_migrate_FROM_local ('USERS');

Note By default, Oracle Database 11g tablespaces are locally managed, with automatic segment space management. When you create this type of tablespace, you can specify default storage parameters, like INITIAL, NEXT, PCTINCREASE, MINEXTENTS, or MAXEXTENTS, but the database will ignore the settings.

Extent Allocation and Deallocation

An Oracle extent consists of a set of contiguous data blocks, which are the smallest unit of space allocation in Oracle. Each Oracle data block corresponds to a specific number of bytes of disk space. Each of your database tables and indexes is called a segment, which is a set of extents allocated for a specific data structure.

Each partition of a table or index has its own segment (and besides table and index segments, you also have rollback, temporary, and undo segments in an Oracle database).

When Oracle needs to allocate an extent to a segment, it first selects a candidate datafile and searches the datafile’s bitmap for the required number of adjacent free blocks. If it can’t find the necessary free space in that datafile, Oracle will look in another datafile, or if there are no more, it will issue an error stating that it is out of free space.

Once Oracle allocates space to a segment by allocating a certain number of extents to it, that space will remain with the extent unless you make an effort to deallocate it. If you truncate a table with the DROP STORAGE option (TRUNCATE TABLE table_name DROP STORAGE), for example, Oracle deallocates the allocated extents. You can also manually deallocate unused extents using the following command:

SQL> ALTER TABLE table_name DEALLOCATE UNUSED;

Storage Parameters

Here is how Oracle determines extent sizing and extent allocation when you create tablespaces:

• The default number of extents is 1. You can override it by specifying MINEXTENTS during tablespace creation.

• You don’t have to provide a value to the MAXEXTENTS parameter when you use locally managed tablespaces. Under locally managed tablespaces, the MAXEXTENTS parameter is set to UNLIMITED, and you don’t have to configure it at all.

• If you choose UNIFORM extent size, the size of all extents, including the first, will be determined by the extent size you choose.

• Initial extent: This storage parameter determines the initial amount of space that is allocated to any object you create in this tablespace. For example, if you specify a UNIFORM extent size of 10MB and specify an INITIAL_EXTENT value of 20MB, Oracle will create two 10MB-sized extents, to start with, for a new object.

• Next extent: The NEXT_EXTENT storage parameter determines the size of the subsequent extents after the initial extent is created.

• Extent management: This column can show a value of LOCAL or DICTIONARY, for locally

managed and dictionary-managed tablespaces, respectively.

• Allocation type: This column refers to the extent allocation, which can have a value of UNIFORM for uniform extent allocation, or SYSTEM for the AUTOALLOCATE option for sizing extents.

• Segment space management: This column shows the segment space management for the tablespace, which can be AUTO (the default) or MANUAL.

Overview of Segments

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace, such as a table or index. As a result, this is typically the smallest unit of storage that an end user of the database will deal with. Four types of segments are found in an Oracle database: data segments, index segments, temporary segments, and rollback segments.

Data Segments

Every table in the database resides in a single data segment, consisting of one or more extents;

Oracle allocates more than one segment for a table if it is a partitioned table or a clustered table .

Oracle creates this data segment when you create the table or cluster with the CREATE statement.

Index Segments

Every non-partitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data.

Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX statement. In this statement, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment.

Temporary Segment

When a user executes commands such as CREATE INDEX, SELECT DISTINCT, and

SELECT GROUP BY, the Oracle server tries to perform sorts in memory. When a sort

needs more space than the space available in memory, intermediate results are written to

the disk. Temporary segments are used to store these intermediate results.Oracle automatically allocates this disk space called a temporary segment. Oracle does not create a segment if the sorting operation can be done in memory .

Undo Segment

An undo segment is used by a transaction that is making changes to a database. Before

changing the data or index blocks, the old value is stored in the undo segment. This allows

a user to undo changes made. Oracle uses the undo to do the following:

· Rollback an active transaction

· Recover a terminated transaction

· Provide read consistency

· Recovery from logical corruptions

Automatic undo management is undo-tablespace based. You allocate space in the form of an undo tablespace, instead of allocating many rollback segments in different sizes.

Overview of Data Blocks

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter. In addition, you can specify of up to five nonstandard block sizes. The data block sizes should be a multiple of the operating system's block size within the maximum limit to avoid unnecessary I/O. Oracle data blocks are the smallest units of storage that Oracle can use or allocate.

Data Block Format

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2 illustrates the format of a data block.

Figure 2-2 Data Block Format

Header (Common and Variable)

The header contains general block information, such as the block address and the type of segment (for example, data or index).

Table Directory

This portion of the data block contains information about the table having rows in this block.

Row Directory

This portion of the data block contains information about the actual rows in the block (including addresses for each row piece in the row data area).

After the space has been allocated in the row directory of a data block's overhead, this space is not reclaimed when the row is deleted. Therefore, a block that is currently empty but had up to 50 rows at one time continues to have 100 bytes allocated in the header for the row directory. Oracle reuses this space only when new rows are inserted in the block.

Overhead

The data block header, table directory, and row directory are referred to collectively as overhead. Some block overhead is fixed in size; the total block overhead size is variable. On average, the fixed and variable portions of data block overhead total 84 to 107 bytes.

Row Data

This portion of the data block contains table or index data. Rows can span blocks.

Row Chaining and Migrating

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG ROW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

TEMPORARY TABLESPACE MANAGEMENT

Shrinking Temporary Tablespaces:

You may have to increase the size of a temporary tablespace to accommodate an unusually large job that makes use of the temporary tablespace. After the completion of the job, you can shrink the temporary tablespace using the clause SHRINK SPACE in an ALTER TABLESPACE statement. Here’s an example:

SQL> alter tablespace temp shrink space;

Tablespace altered.

The SHRINK SPACE clause will shrink all tempfiles to a minimum size, which is about 1MB. You can employ the KEEP clause to specify a minimum size for the tempfiles, as shown here:

SQL> ALTER tablespace temp SHRINK SPACE KEEP 250m;

Default Temporary Tablespace

When you create database users, you must assign a default temporary tablespace in which they can perform their temporary work, such as sorting. If you neglect to explicitly assign a temporary tablespace, the users will use the critical System tablespace as their temporary tablespace, which could lead to fragmentation of that tablespace, besides filling it up and freezing database activity.

Note that if you didn’t create a default temporary tablespace while creating your database, it isn’t too late to do so later. You can just create a temporary tablespace, as shown in the preceding example, and make it the default temporary tablespace for the database, with a statement like this:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temptbs02;

You can find out the name of the current default temporary tablespace for your database by executing the following query:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE

FROM database_properties

WHERE property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME PROPERTY_VALUE

----------------------- -----------------

DEFAULT_TEMP_TABLESPACE TEMP

Temporary Tablespace Groups

Large transactions can sometimes run out of temporary space. Large sort jobs, especially those involving tables with many partitions, lead to heavy use of the temporary tablespaces, thus potentially leading to a performance hit. Oracle Database 10g introduced the concept of a temporary tablespace group, which allows a user to utilize multiple temporary tablespaces simultaneously in different sessions.

Here are some of the main characteristics of a temporary tablespace group:

• A temporary tablespace group must consist of at least one tablespace. There is no explicit maximum number of tablespaces.

• If you delete all members from a temporary tablespace group, the group is automatically deleted as well.

• A temporary tablespace group has the same namespace as the temporary tablespaces that are part of the group.

• The name of a temporary tablespace cannot be the same as the name of any tablespace group.

• When you assign a temporary tablespace to a user, you can use the temporary tablespace group name instead of the actual temporary tablespace name. You can also use the temporary tablespace group name when you assign the default temporary tablespace for the database.

Benefits of Temporary Tablespace Groups:

Using a temporary tablespace group, rather than the usual single temporary tablespace, provides several benefits:

• SQL queries are less likely to run out of sort space because the query can now simultaneously use several temporary tablespaces for sorting.

• You can specify multiple default temporary tablespaces at the database level.

• Parallel execution servers in a parallel operation will efficiently utilize multiple temporary tablespaces.

• A single user can simultaneously use multiple temporary tablespaces in different sessions.

COMMANDS OF TEMP TABLESPACE GROUP:

CREATE GROUP:

SQL> CREATE TEMPORARY TABLESPACE temp01

TEMPFILE '/u01/oracle/oradata/temp01_01.dbf'

SIZE 500M TABLESPACE GROUP tmpgrp1;

SPECIFY THE SAME GROUP:

SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp1;

If you specify a pair of quotes ('') for the tablespace group name, you are implicitly telling Oracle not to allocate that temporary tablespace to a tablespace group. Here’s an example:

SQL> CREATE TEMPORARY TABLESPACE temp02 TEMPFILE '/u01/oracle/oradata/temp02_01.dbf' SIZE 500M TABLESPACE GROUP '';

Adding a Tablespace to a Temporary Tablespace Group:

SQL> ALTER TABLESPACE temp02 TABLESPACE GROUP tmpgrp2;

Assigning Temporary Tablespace Groups When Creating and Altering Users

SQL> CREATE USER salapati IDENTIFIED BY sammyy1

DEFAULT TABLESPACE users

TEMPORARY TABLESPACE tmpgrp1;

Once you create a user, you can also use the ALTER USER statement to change the temporary tablespace group of the user. Here’s a SQL statement that does this:

SQL> ALTER USER salapati TEMPORARY TABLESPACE tmpgrp2;

Viewing Temporary Tablespace Group Information:

SQL> SELECT group_name, tablespace_name

FROM dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

---------- ---------------

TMPGRP1 TEMP01

SQL> SELECT username, temporary_tablespace

FROM dba_users;

USERNAME TEMPORARY_TABLESPACE

-------- ---------------------

SYS TEMP

SYSTEM TEMP

SAM TMPGRP1

SCOTT TEMP

Creating a Bigfile Tablespace

To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...). Oracle Database automatically creates a locally managed tablespace with automatic segment space management. You can, but need not, specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in this statement. However, the database returns an error if you specify EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the statement is the same as for the CREATE TABLESPACE statement, but you can only specify one datafile. For example:

CREATE BIGFILE TABLESPACE bigtbs

    DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G

You can specify SIZE in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T).

If the default tablespace type was set to BIGFILE at database creation, you need not specify the keyword BIGFILE in the CREATE TABLESPACE statement. A bigfile tablespace is created by default.

If the default tablespace type was set to BIGFILE at database creation, but you want to create a traditional (smallfile) tablespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.

Physical Structures Limits

Item	Type of Limit	Limit Value
Database Block Size	Minimum	2048 bytes; must be a multiple of operating system physical block size
Database Block Size	Maximum	Operating system dependent; never more than 32 KB
Database Blocks	Minimum in initial extent of a segment	2 blocks
Database Blocks	Maximum per datafile	Platform dependent; typically 2²² - 1 blocks
Controlfiles	Number of control files	1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles	Size of a control file	Dependent on operating system and database creation options; maximum of20,000 x (database block size)
Database files	Maximum per tablespace	Operating system dependent; usually 1022
Database files	Maximum per database	65533 May be less on some operating systems Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents	Maximum per dictionary managed tablespace	4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents	Maximum per locally managed (uniform) tablespace	2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size	Maximum	Operating system dependent. Limited by maximum operating system file size; typically 2²² or 4 MB blocks
MAXEXTENTS	Default value	Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS	Maximum	Unlimited
Redo Log Files	Maximum number of logfiles	Limited by value of MAXLOGFILES parameter in the CREATE DATABASEstatement Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files	Maximum number of logfiles per group	Unlimited
Redo Log File Size	Minimum size	4 MB
Redo Log File Size	Maximum Size	Operating system limit; typically 2 GB
Tablespaces	Maximum number per database	64 K Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces	Number of blocks	A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 2³² ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces	Number of blocks	A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (2²²) blocks.
External Tables file	Maximum size	Dependent on the operating system. An external table can be composed of multiple files.

Some Important views

1.To view Tablespace Name

SQL> select * from v$tablespace;

2. To see the parameters and their datatypes

SQL> desc v$tablespace

Name Null? Type

----------------------------------------- -------- ----------------------------

TS# NUMBER

NAME VARCHAR2(30)

INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)

BIGFILE VARCHAR2(3)

FLASHBACK_ON VARCHAR2(3)

ENCRYPT_IN_BACKUP VARCHAR2(3)

3. To See the datafiles name and their creation time

SQL>select FILE#,CREATION_TIME,TS#,STATUS,BYTES,NAME from v$datafile;

4. To See the datafiles and their sizes

SQL> select file_name, file_id, tablespace_name, sum(bytes)/1024/1024, autoextensible, sum(maxbytes)/1024/1024 from dba_data_files group by file_name, file_id, tablespace_name, autoextensible;

5.To view the sizes: