Creating Tablespaces - Oracle 10g

Before you can create a tablespace, you must create a database to contain it. The primary tablespace in any database is the SYSTEM tablespace, which contains information basic to the functioning of the database server, such as the data dictionary and the system rollback segment. The SYSTEM tablespace is the first tablespace created at database creation. It is managed as any other tablespace, but requires a higher level of privilege and is restricted in some ways. For example, you cannot rename or drop the SYSTEM tablespace or take it offline.

The SYSAUX tablespace, which acts as an auxiliary tablespace to the SYSTEM tablespace, is also always created when you create a database. It contains information about and the schemas used by various Oracle products and features, so that those products do not require their own tablespaces. As for the SYSTEM tablespace management of the SYSAUX tablespace requires a higher level of security and you cannot rename or drop it.

The steps for creating tablespaces vary by operating system, but the first step is always to use your operating system to create a directory structure in which your datafiles will be allocated. On most operating systems, you specify the size and fully specified filenames of datafiles when you create a new tablespace or alter an existing tablespace by adding datafiles. Whether you are creating a new tablespace or modifying an existing one, the database automatically allocates and formats the datafiles as specified.

To create a new tablespace, use the SQL statement CREATE TABLESPACE or CREATE TEMPORARY TABLESPACE. You must have the CREATE TABLESPACE system privilege to create a tablespace. Later, you can use the ALTER TABLESPACE or ALTER DATABASE statements to alter the tablespace. You must have the ALTER TABLESPACE or ALTER DATABASE system privilege, correspondingly.

You can also use the CREATE UNDO TABLESPACE statement to create a special type of tablespace called an undo tablespace, which is specifically designed to contain undo records. These are records generated by the database that are used to roll back, or undo, changes to the database for recovery, read consistency, or as requested by a ROLLBACK statement.

The creation and maintenance of permanent and temporary tablespaces are discussed in the following sections:

  • Locally Managed Tablespaces
  • Bigfile Tablespaces
  • Dictionary-Managed Tablespaces
  • Temporary Tablespaces
  • Multiple Temporary Tablespaces: Using Tablespace Groups

Locally Managed Tablespaces

Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

  • Concurrency and speed of space operations is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
  • Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
  • Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
  • Space allocation is simplified, because when the AUTOALLOCATE clause is specified, the database automatically selects the appropriate extent size.
  • User reliance on the data dictionary is reduced, because the necessary information is stored in file headers and bitmap blocks.
  • Coalescing free extents is unnecessary for locally managed tablespaces.

All tablespaces, including the SYSTEM tablespace, can be locally managed. The DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.

Creating a Locally Managed Tablespace

You create a locally managed tablespace by specifying LOCAL in the EXTENT MANAGEMENT clause of the CREATE TABLESPACE statement. This is the default for new permanent tablespaces, but you must specify if it you want to specify the management of the locally managed tablespace. You can have the database manage extents for you automatically with the AUTOALLOCATE clause (the default), or you can specify that the tablespace is managed with uniform extents of a specific size (UNIFORM).

If you expect the tablespace to contain objects of varying sizes requiring many extents with different extent sizes, then AUTOALLOCATE is the best choice. AUTOALLOCATE is also a good choice if it is not important for you to have a lot of control over space allocation and deallocation, because it simplifies tablespace management. Some space may be wasted with this setting, but the benefit of having Oracle Database manage your space most likely outweighs this drawback.

If you want exact control over unused space, and you can predict exactly the space to be allocated for an object or objects and the number and size of extents, then UNIFORM is a good choice. This setting ensures that you will never have unusable space in your tablespace.

When you do not explicitly specify the type of extent management, Oracle Database determines extent management as follows:

  • If the CREATE TABLESPACE statement omits the DEFAULT storage clause, then the database creates a locally managed autoallocated tablespace.
  • If the CREATE TABLESPACE statement includes a DEFAULT storage clause, then the database considers the following:
  • If you specified the MINIMUM EXTENT clause, the database evaluates whether the values of MINIMUM EXTENT, INITIAL, and NEXT are equal and the value of CTINCREASE is 0. If so, the database creates a locally managed uniform tablespace with extent size = INITIAL. If the MINIMUM EXTENT, INITIAL, and NEXT parameters are not equal, or if PCTINCREASE is not 0, the database ignores any extent storage parameters you may specify and creates a locally managed, autoallocated tablespace.
  • If you did not specify MINIMUM EXTENT clause, the database evaluates only whether the storage values of INITIAL and NEXT are equal and PCTINCREASE is 0. If so, the tablespace is locally managed and uniform. Otherwise, the tablespace is locally managed and autoallocated.

The following statement creates a locally managed tablespace named lmtbsb and specifies AUTOALLOCATE:

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K. In contrast, dictionary-managed tablespaces have a minimum extent size of two database blocks. Therefore, in systems with block size smaller than 32K, autoallocated locally managed tablespace will be larger initially.

The alternative to AUTOALLOCATE is UNIFORM. which specifies that the tablespace is managed with extents of uniform size. You can specify that size in the SIZE clause of UNIFORM. If you omit SIZE, then the default size is 1M.

The following example creates a tablespace with uniform 128K extents. (In a database with 2K blocks, each extent would be equivalent to 64 database blocks). Each 128K extent is represented by a bit in the extent bitmap for this file.

You cannot specify the DEFAULT storage clause, MINIMUM EXTENT, or TEMPORARY when you explicitly specify EXTENT MANAGEMENT LOCAL. If you want to create a temporary locally managed tablespace, use the CREATE TEMPORARY TABLESPACE statement.

Specifying Segment Space Management in Locally Managed Tablespaces
When you create a locally managed tablespace using the CREATE TABLESPACE statement, the SEGMENT SPACE MANAGEMENT clause lets you specify how free and used space within a segment is to be managed. You can choose either manual or automatic segment-space management.

  • MANUAL: Manual segment-space management uses free lists to manage free space within segments. Free lists are lists of data blocks that have space available for inserting rows. With this form of segment-space management, you must specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace. MANUAL is the default.
  • AUTO: Automatic segment-space management uses bitmaps to manage the free space within segments. The bitmap describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. These bitmaps allow the database to manage free space automatically.

You can specify automatic segment-space management only for permanent, locally managed tablespaces. Automatic segment-space management is a simpler and more efficient way of managing space within a segment. It completely eliminates any need to specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace. If you specify these attributes, the database ignores them.

Automatic segment-space management delivers better space utilization than manual segment-space management. It is also self-tuning, in that it scales with increasing number of users or instances. In a Real Application Clusters environment, automatic segment-space management allows for a dynamic affinity of space to instances, thus avoiding the hard partitioning of space inherent with using free list groups. In addition, for many standard workloads, application performance with automatic segment-space management is better than the performance of a well-tuned application using manual segment-space management. The following statement creates tablespace lmtbsb with automatic segment-space management:

The segment-space management you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot subsequently change the segment-space management mode of a tablespace. Locally managed tablespaces using automatic segment-space management can be created as single-file, or bigfile, tablespaces.

Altering a Locally Managed Tablespace

You cannot alter a locally managed tablespace to a locally managed temporary tablespace, nor can you change its method of segment-space management. Coalescing free extents is unnecessary for locally managed tablespaces. However, you can use the ALTER TABLESPACE statement on locally managed tablespaces for some operations, including the following:

  • Adding a datafile. For example:

  • Altering tablespace availability (ONLINE/OFFLINE).
  • Making a tablespace read-only or read/write.
  • Renaming a datafile, or enabling or disabling the autoextension of the size of a datafile in the tablespace Bigfile Tablespaces

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the following:

  • A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
  • Bigfile tablespaces can reduce the number of datafiles needed for a database. An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.
  • Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace can be bigfile tablespaces even if their segments are manually managed.

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-spec 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:

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.

Altering a Bigfile Tablespace

Two clauses of the ALTER TABLESPACE statement support datafile transparency when you are using bigfile tablespaces:

  • RESIZE: The RESIZE clause lets you resize the single datafile in a bigfile tablespace to an absolute size, without referring to the datafile. For example:
  • AUTOEXTEND (used outside of the ADD DATAFILE clause): With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD DATAFILE clause. For example:

An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.

Identifying a Bigfile Tablespace

The following views contain a BIGFILE column that identifies a tablespace as a bigfile tablespace:


You can also identify a bigfile tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on OS/390.

Dictionary-Managed Tablespaces

The default for extent management when creating a tablespace is locally managed. However, you can explicitly specify a dictionary-managed tablespace. For dictionary-managed tablespaces, the database updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse.

Creating a Dictionary-Managed Tablespace

The following statement creates the dictionary-managed tablespace tbsa:

The tablespace has the following characteristics:

  • The data of the new tablespace is contained in a single datafile, 50M in size.
  • The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.
  • The default storage parameters for any segments created in this tablespace are specified.

The parameters specified in the preceding example determine segment storage allocation in the tablespace. These parameters affect both how long it takes to access data stored in the database and how efficiently space in the database is used. They are referred to as storage parameters, and are described in the following table:

The MINIMUM EXTENT parameter on the CREATE TABLESPACE statement also influences segment allocation. If specified, it ensures that all free and allocated extents in the tablespace are at least as large as, and a multiple of, a specifiednumber of bytes. This clause provides one way to control free space fragmentation in the tablespace.

Specifying Tablespace Default Storage Parameters

When you create a new dictionary-managed tablespace, you can specify default storage parameters for objects that will be created in the tablespace. Storage parameter specified when an object is created override the default storage parameters of the tablespace containing the object. If you do not specify storage parameters when creating an object, the object segment automatically uses the default storage parameters for the tablespace.

Set the default storage parameters for a tablespace to account for the size of a typical object that the tablespace will contain (you estimate this size). You can specify different storage parameters for an unusual or exceptional object when creating that object. You can also alter your default storage parameters at a later time.

You cannot specify default storage parameters for tablespaces that are specifically created as locally managed.

Altering a Dictionary-Managed Tablespace

One common change to a database is adding a datafile. The following statement creates a new datafile for the tbsa tablespace:

You can also change the default storage parameters of a tablespace using the ALTER TABLESPACE statement, as illustrated in the following example:

New values for the default storage parameters of a tablespace affect only objects that are subsequently created, or extents subsequently allocated for existing segments within the tablespace.

Other reasons for issuing an ALTER TABLESPACE statement include, but are not limited to:

  • Coalescing free space in a tablespace
  • Altering tablespace availability (ONLINE/OFFLINE).
  • Making a tablespace read-only or read/write.
  • Adding or renaming a datafile, or enabling/disabling the autoextension of the size of a datafile in the tablespace.

Coalescing Free Space in Dictionary-Managed Tablespaces

Over time, the free space in a dictionary-managed tablespace can become fragmented, making it difficult to allocate new extents. This section discusses how to defragment free space and includes the following topics:

  • How Oracle Database Coalesces Free Space
  • Manually Coalescing Free Space
  • Monitoring Free Space

How Oracle Database Coalesces Free Space

A free extent in a dictionary-managed tablespace is made up of a collection of contiguous free blocks. When allocating new extents to a tablespace segment, the database uses the free extent closest in size to the required extent. In some cases, when segments are dropped, their extents are deallocated and marked as free, but adjacent free extents are not immediately recombined into larger free extents. The result is fragmentation that makes allocation of larger extents more difficult.

Oracle Database addresses fragmentation in several ways:

When attempting to allocate a new extent for a segment, the database first tries to find a free extent large enough for the new extent. Whenever the database cannot find a free extent that is large enough for the new extent, it coalesces adjacent free extents in the tablespace and looks again.

  • The SMON background process periodically coalesces neighboring free extents when the PCTINCREASE value for a tablespace is not zero. If you set PCTINCREASE=0, no coalescing of free extents occurs. If you are concerned about the overhead of ongoing coalesce operations of SMON, an alternative is to set PCTINCREASE=0, and periodically coalesce free space manually.
  • When a segment is dropped or truncated, a limited form of coalescing is performed if the PCTINCREASE value for the segment is not zero. This is done even if PCTINCREASE=0 for the tablespace containing the segment.
  • You can use the ALTER TABLESPACE ... COALESCE statement to manually coalesce any adjacent free extents.

The process of coalescing free space is illustrated in the following figure.

Coalescing Free Space

Coalescing Free Space

Manually Coalescing Free Space

If you find that fragmentation of space in a tablespace is high (contiguous space on your disk appears as noncontiguous), you can coalesce any free space using the ALTER TABLESPACE ... COALESCE statement. You must have the ALTER TABLESPACE system privilege to coalesce tablespaces.

This statement is useful if PCTINCREASE=0, or you can use it to supplement SMON and extent allocation coalescing. If all extents within the tablespace are of the same size, coalescing is not necessary. This is the case when the default PCTINCREASE value for the tablespace is set to zero, all segments use the default storage parameters of the tablespace, and INITIAL=NEXT=MINIMUM EXTENT.

The following statement coalesces free space in the tablespace tabsp_4:


The COALESCE clause of the ALTER TABLESPACE statement is exclusive. You cannot specify any other clause in the same ALTER TABLESPACE statement.

Monitoring Free Space The following views provide information on the free space in a tablespace:


The following statement displays the free space in tablespace tabsp_4:


This view shows that there is adjacent free space in tabsp_4 (for example, blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the ALTER TABLESPACE statement shown previously, the results of this query would read:


The DBA_FREE_SPACE_COALESCED view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.

Temporary Tablespaces

A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrence of multiple sort operations, reduce their overhead, and avoid Oracle Database space management operations. A temporary tablespace can be assigned to users with the CREATE USER or ALTER USER statement and can be shared by multiple users.

Within a temporary tablespace, all sort operations for a given instance and tablespace share a single sort segment. Sort segments exist for every instance that performs sort operations within a given tablespace. The sort segment is created by the first statement that uses a temporary tablespace for sorting, after startup, and is released only at shutdown. An extent cannot be shared by multiple transactions.

You can view the allocation and deallocation of space in a temporary tablespace sort segment using the V$SORT_SEGMENT view. The V$TEMPSEG_USAGE view identifies the current sort users in those segments. You cannot explicitly create objects in a temporary tablespace.

Creating a Locally Managed Temporary Tablespace

Because space management is much simpler and more efficient in locally managed tablespaces, they are ideally suited for temporary tablespaces. Locally managed temporary tablespaces use tempfiles, which do not modify data outside of the temporary tablespace or generate any = redo for temporary tablespace data. Because of this, they enable you to perform =on-disk sorting operations in a read-only or standby database.

You also use different views for viewing =information about tempfiles than you would for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.

To create a locally managed temporary =tablespace, you use the CREATE TEMPORARY TABLESPACE statement, which requires =that you have the CREATE TABLESPACE system privilege.

The following statement creates a = temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by =a bit in the bitmap for the file.

The extent management clause is optional = for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The Oracle Database default for SIZE = is 1M. But if you want to specify another value for SIZE, you can do so as =shown in the preceding statement.

The AUTOALLOCATE clause is not allowed for temporary tablespaces.

Creating a Bigfile Temporary Tablespace

Just as for regular tablespaces, you can create single-file (bigfile) temporary tablespaces. Use the CREATE BIGFILE TEMPORARY TABLESPACE statement to create a single-tempfile tablespace.

Altering a Locally Managed Temporary Tablespace

Except for adding a tempfile, as illustrated in the following example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace.

The following statement resizes a temporary file:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a temporary file and deletes the operating system file:

The tablespace to which this tempfile belonged remains. A message is written to the alert file for the datafile that was deleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the error is written to the alert file. It is also possible, but not shown, to use the ALTER DATABASE statement to enable or disable the automatic extension of an existing tempfile, and to rename (RENAME FILE) a tempfile.

Creating a Dictionary-Managed Temporary Tablespace

In earlier releases, you could create a dictionary-managed temporary tablespace by specifying the TEMPORARY keyword after specifying the tablespace name in a CREATE TABLESPACE statement. This syntax has been deprecated. It is still supported in case you are using dictionary-managed tablespaces, which are not supported by the CREATE TEMPORARY TABLEPSPACE syntax. If you do use this deprecated syntax, the extent cannot be locally managed, nor can you specify a nonstandard block size for the tablespace.

Oracle strongly recommends that you create locally managed temporary tablespaces containing tempfiles, as described in the preceding sections. The creation of new dictionary-managed tablespaces is scheduled for desupport.

Altering a Dictionary-Managed Temporary Tablespace

You can issue the ALTER TABLESPACE statement against a dictionary-managed temporary tablespace using many of the same keywords and clauses as for a permanent dictionary-managed tablespace. Restrictions are noted in the Oracle Database SQL Reference.

You can change an existing permanent dictionary-managed tablespace to a temporary tablespace, using the ALTER TABLESPACE statement. For example:


Multiple Temporary Tablespaces: Using Tablespace Groups

A tablespace group enables a user to consume temporary space from multiple tablespaces. A tablespace group has the following characteristics:

  • It contains at least one tablespace. There is no explicit limit on the maximum number of tablespaces that are contained in a group.
  • It shares the namespace of tablespaces, so its name cannot be the same as any tablespace.
  • You can specify a tablespace group name wherever a tablespace name would appear when you assign a default temporary tablespace for the database or a temporary tablespace for a user.

You do not explicitly create a tablespace group. Rather, it is created implicitly when you assign the first temporary tablespace to the group. The group is deleted when the last temporary tablespace it contains is removed from it.

Using a tablespace group, rather than a single temporary tablespace, can alleviate problems caused where one tablespace is inadequate to hold the results of a sort, particularly on a table that has many partitions. A tablespace group enables parallel execution servers in a single parallel operation to use multiple temporary tablespaces.

The view DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.

Creating a Tablespace Group

You create a tablespace group implicitly when you include the TABLESPACE GROUP clause in the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement and the specified tablespace group does not currently exist. For example, if neither group1 nor group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:

Changing Members of a Tablespace Group

You can add a tablespace to an existing tablespace group by specifying the existing group name in the TABLESPACE GROUP clause of the CREATE TEMPORARY TABLESPACE or ALTER TABLESPACE statement.

The following statement adds a tablespace to an existing group. It creates and adds tablespace lmtemp3 to group1, so that group1 contains tablespaces lmtemp2 and lmtemp3.

The following statement also adds a tablespace to an existing group, but in this case because tablespace lmtemp2 already belongs to group1, it is in effect moved from group1 to group2:


Now group2 contains both lmtemp and lmtemp2, while group1 consists of only tmtemp3. You can remove a tablespace from a group as shown in the following statement:


Tablespace lmtemp3 no longer belongs to any group. Further, since there are no longer any members of group1, this results in the implicit deletion of group1.

Assigning a Tablespace Group as the Default Temporary Tablespace

Use the ALTER DATABASE ...DEFAULT TEMPORARY TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:


Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces lmtemp and lmtemp2.

If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first be remove from the tablespace from the tablespace group. Likewise, you cannot drop a single temporary as long as it is the default temporary tablespace.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 10g Topics