|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
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.
SYSAUX tablespace, which acts as an auxiliary tablespace to the
SYSTEM tablespace, is also always created when you create a database. It contains 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 management of the
SYSAUX tablespace is discussed separately in "Managing the SYSAUX Tablespace".
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. Creating and managing undo tablespaces is the subject of Chapter 16, "Managing Undo".
The creation and maintenance of permanent and temporary tablespaces are discussed in the following sections:
Chapter 2, "Creating and Configuring an Oracle Database" and your Oracle Database installation documentation for your operating system for information about tablespaces that are created at database creation
Oracle Database SQL Language Reference for more information about the syntax and semantics of the
CREATE TEMPORARY TABLESPACE,
ALTER TABLESPACE, and
ALTER DATABASE statements.
"Specifying Database Block Sizes" for information about initialization parameters necessary to create tablespaces with nonstandard block sizes
Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:
Fast, concurrent space operations. Space allocations and deallocations modify locally managed resources (bitmaps stored in header files).
Readable standby databases are allowed, because locally managed temporary tablespaces 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.
DBMS_SPACE_ADMIN package provides maintenance procedures for locally managed tablespaces.
"Bigfile Tablespaces" for information about creating another type of locally managed tablespace that contains only a single datafile or tempfile.
Oracle Database PL/SQL Packages and Types Reference for information on the
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 the
LOCAL clause if you want to specify either the
AUTOALLOCATE clause or the
UNIFORM clause. 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 (
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:
CREATE TABLESPACE statement omits the
DEFAULT storage clause, then the database creates a locally managed autoallocated tablespace.
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
NEXT are equal and the value of
PCTINCREASE is 0. If so, the database creates a locally managed uniform tablespace with extent size =
INITIAL. If the
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
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
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.
The alternative to
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.
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
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.
Note:When you allocate a datafile for a locally managed tablespace, you should allow space for metadata used for space management (the extent bitmap or space header segment) which are part of user space. For example, if you specify the
UNIFORMclause in the extent management clause but you omit the
SIZEparameter, then the default extent size is 1MB. In that case, the size specified for the datafile must be larger (at least one block plus space for the bitmap) than 1MB.
In a locally managed tablespace, there are two methods that Oracle Database can use to manage segment space: automatic and manual. Manual segment space management uses linked lists called "freelists" to manage free space in the segment, while automatic segment space management uses bitmaps. Automatic segment space management is the more efficient method, and is the default for all new permanent, locally managed tablespaces.
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 an Oracle Real Application Clusters environment, automatic segment space management allows for a dynamic affinity of space to instances. 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.
Although automatic segment space management is the default for all new permanent, locally managed tablespaces, you can explicitly enable it with the
SEGMENT SPACE MANAGEMENT AUTO clause.
The following statement creates tablespace
lmtbsb with automatic segment space management:
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SEGMENT SPACE MANAGEMENT MANUAL clause disables automatic segment space management.
The segment space management that you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot change the segment space management mode of a tablespace.
If you set extent management to
UNIFORM, then you must ensure that each extent contains at least 5 database blocks.
If you set extent management to
AUTOALLOCATE, and if the database block size is 16K or greater, then Oracle manages segment space by creating extents with a minimum size of 5 blocks rounded up to 64K.
Locally managed tablespaces using automatic segment space management can be created as single-file or bigfile tablespaces, as described in "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
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
Bigfile tablespaces are intended to be used with Automatic Storage Management (Oracle ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.
Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.
To create a bigfile tablespace, specify the
BIGFILE keyword of the
TABLESPACE statement (
TABLESPACE ...). Oracle Database automatically creates a locally managed tablespace with automatic segment space management. You can, but need not, specify
AUTO in this statement. However, the database returns an error if you specify
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
TABLESPACE statement to override the default tablespace type for the tablespace that you are creating.
The following views contain a
BIGFILE column that identifies a tablespace as a bigfile tablespace:
You can specify that all tables created in a tablespace are compressed by default. You specify the type of table compression using the
DEFAULT keyword, followed by one of the compression type clauses used when creating a table.
The following statement indicates that all tables created in the tablespace are to use OLTP compression, unless otherwise specified:
CREATE TABLESPACE ... DEFAULT COMPRESS FOR OLTP ... ;
You can override the default tablespace compression specification when you create a table in that tablespace.
You can encrypt any permanent tablespace to protect sensitive data. Tablespace encryption is completely transparent to your applications, so no application modification is necessary. Encrypted tablespaces primarily protect your data from unauthorized access by means other than through the database. For example, when encrypted tablespaces are written to backup media for travel from one Oracle database to another or for travel to an off-site facility for storage, they remain encrypted. Also, encrypted tablespaces protect data from users who try to circumvent the security features of the database and access database files directly through the operating system file system.
Tablespace encryption does not address all security issues. It does not, for example, provide access control from within the database. Any user who is granted privileges on objects stored in an encrypted tablespace can access those objects without providing any kind of additional password or key.
When you encrypt a tablespace, all tablespace blocks are encrypted. All segment types are supported for encryption, including tables, clusters, indexes, LOBs (
SECUREFILE), table and index partitions, and so on.
Note:There is no need to use LOB encryption on
SECUREFILELOBs stored in an encrypted tablespace.
To maximize security, data from an encrypted tablespace is automatically encrypted when written to the undo tablespace, to the redo logs, and to any temporary tablespace. There is no need to explicitly create encrypted undo or temporary tablespaces, and in fact, you cannot specify encryption for those tablespace types.
For partitioned tables and indexes that have different partitions in different tablespaces, it is permitted to use both encrypted and non-encrypted tablespaces in the same table or index.
Tablespace encryption uses the transparent data encryption feature of Oracle Database, which requires that you create an Oracle wallet to store the master encryption key for the database. The wallet must be open before you can create the encrypted tablespace and before you can store or retrieve encrypted data. When you open the wallet, it is available to all session, and it remains open until you explicitly close it or until the database is shut down.
To encrypt a tablespace, you must open the database with the
COMPATIBLE initialization parameter set to 11.1.0 or higher. The default setting for
COMPATIBLE for a new Oracle Database 11g Release 2 installation is 11.2.0. Any user who can create a tablespace can create an encrypted tablespace.
Transparent data encryption supports industry-standard encryption algorithms, including the following Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) algorithms:
The encryption key length is implied by the algorithm name. For example, the AES128 algorithm uses 128-bit keys. You specify the algorithm to use when you create the tablespace, and different tablespaces can use different algorithms. Although longer key lengths theoretically provide greater security, there is a trade-off in CPU overhead. If you do not specify the algorithm in your
TABLESPACE statement, AES128 is the default. There is no disk space overhead for encrypting a tablespace.
The following statement creates an encrypted tablespace with the default encryption algorithm:
CREATE TABLESPACE securespace DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M ENCRYPTION DEFAULT STORAGE(ENCRYPT);
The following statement creates the same tablespace with the AES256 algorithm:
CREATE TABLESPACE securespace DATAFILE '/u01/app/oracle/oradata/orcl/secure01.dbf' SIZE 100M ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);
The following are restrictions for encrypted tablespaces:
You cannot encrypt an existing tablespace with an
TABLESPACE statement. However, you can use Data Pump or SQL statements such as
MOVE to move existing table data into an encrypted tablespace.
Encrypted tablespaces are subject to restrictions when transporting to another database. See "Limitations on Transportable Tablespace Use".
When recovering a database with encrypted tablespaces (for example after a
ABORT or a catastrophic error that brings down the database instance), you must open the Oracle wallet after database mount and before database open, so the recovery process can decrypt data blocks and redo.
In addition, see Oracle Database Advanced Security Administrator's Guide for general restrictions for transparent data encryption.
Querying Tablespace Encryption Information
USER_TABLESPACES data dictionary views include a column named
ENCRYPTED. This column contains
YES for encrypted tablespaces.
SELECT t.name, e.encryptionalg algorithm FROM v$tablespace t, v$encrypted_tablespaces e WHERE t.ts# = e.ts#; NAME ALGORITHM ------------------------------ --------- SECURESPACE AES256
Oracle Database 2 Day + Security Guide for more information about transparent data encryption and for instructions for creating and opening wallets
"Consider Encrypting Columns That Contain Sensitive Data" for an alternative to encrypting an entire tablespace
Oracle Real Application Clusters Administration and Deployment Guide for information on using an Oracle wallet in an Oracle Real Application Clusters environment
Oracle Database SQL Language Reference for information about the
A temporary tablespace contains transient data that persists only for the duration of the session. Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory and can improve the efficiency of space management operations during sorts.
Temporary tablespaces are used to store the following:
Intermediate sort results
Temporary tables and temporary indexes
Within a temporary tablespace, all sort operations for a particular instance share a single sort segment, and sort segments exist for every instance that performs sort operations that require temporary space. A sort segment is created by the first statement after startup that uses the temporary tablespace for sorting, and is released only at shutdown.
By default, a single temporary tablespace named
TEMP is created for each new Oracle Database installation. You can create additional temporary tablespaces with the
TABLESPACE statement. You can assign a temporary tablespace to each database user with the
CREATE USER or
ALTER USER statement. A single temporary tablespace can be shared by multiple users.
You cannot explicitly create objects in a temporary tablespace.
Note:The exception to the preceding statement is a temporary table. When you create a temporary table, its rows are stored in your default temporary tablespace, unless you create the table in a new temporary tablespace. See "Creating a Temporary Table" for more information.
Default Temporary Tablespace
Users who are not explicitly assigned a temporary tablespace use the database default temporary tablespace, which for new installations is
TEMP. You can change the default temporary tablespace for the database with the following command:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;
To determine the current default temporary tablespace for the database, run the following query:
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'; PROPERTY_NAME PROPERTY_VALUE -------------------------- ------------------------------ DEFAULT_TEMP_TABLESPACE TEMP
Space Allocation in a Temporary Tablespace
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.
When a sort operation that uses temporary space completes, allocated extents in the sort segment are not deallocated; they are just marked as free and available for reuse. The
DBA_TEMP_FREE_SPACE view displays the total allocated and free space in each temporary tablespace. See "Viewing Space Usage for Temporary Tablespaces" for more information. You can manually shrink a locally managed temporary tablespace that has a large amount of unused space. See "Shrinking a Locally Managed Temporary Tablespace" for details.
Oracle Database Security Guide for information about creating users and assigning temporary tablespaces
Oracle Database Concepts for more information about the default temporary tablespace
Oracle Database Reference for more information about the
Oracle Database Performance Tuning Guide for a discussion on tuning sorts
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
DBA_TEMP_FILES views are analogous to the
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.
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
The extent management clause is optional for temporary tablespaces because all temporary tablespaces are created with locally managed extents of a uniform size. The 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.
Note:On some operating systems, the database does not allocate space for the tempfile until the tempfile blocks are actually accessed. This delay in space allocation results in faster creation and resizing of tempfiles, but it requires that sufficient disk space is available when the tempfiles are later used. Please refer to your operating system documentation to determine whether the database allocates tempfile space in this way on your system.
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. See the sections "Creating a Bigfile Tablespace" and "Altering a Bigfile Tablespace" for information about bigfile tablespaces, but consider that you are creating temporary tablespaces that use tempfiles instead of datafiles.
DBA_TEMP_FREE_SPACE dictionary view contains information about space usage for each temporary tablespace. The information includes the space allocated and the free space. You can query this view for these statistics using the following command.
SELECT * from DBA_TEMP_FREE_SPACE; TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE ----------------------------------- --------------- --------------- ---------- TEMP 250609664 250609664 249561088
A tablespace group enables a user to consume temporary space from multiple tablespaces. 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.
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.
DBA_TABLESPACE_GROUPS lists tablespace groups and their member tablespaces.
See Also:Oracle Database Security Guide for more information about assigning a temporary tablespace or tablespace group to a user
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
group2 exists, then the following statements create those groups, each of which has only the specified tablespace as a member:
CREATE TEMPORARY TABLESPACE lmtemp2 TEMPFILE '/u02/oracle/data/lmtemp201.dbf' SIZE 50M TABLESPACE GROUP group1; ALTER TABLESPACE lmtemp TABLESPACE GROUP group2;
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
group1, so that
group1 contains tablespaces
CREATE TEMPORARY TABLESPACE lmtemp3 TEMPFILE '/u02/oracle/data/lmtemp301.dbf' SIZE 25M TABLESPACE GROUP group1;
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
ALTER TABLESPACE lmtemp2 TABLESPACE GROUP group2;
group2 contains both
group1 consists of only
You can remove a tablespace from a group as shown in the following statement:
ALTER TABLESPACE lmtemp3 TABLESPACE GROUP '';
lmtemp3 no longer belongs to any group. Further, since there are no longer any members of
group1, this results in the implicit deletion of
TABLESPACE statement to assign a tablespace group as the default temporary tablespace for the database. For example:
ALTER DATABASE sample DEFAULT TEMPORARY TABLESPACE group2;
Any user who has not explicitly been assigned a temporary tablespace will now use tablespaces
If a tablespace group is specified as the default temporary tablespace, you cannot drop any of its member tablespaces. You must first remove the tablespace from the tablespace group. Likewise, you cannot drop a single temporary tablespace as long as it is the default temporary tablespace.