Use the datafile_tempfile_spec form of file_specification to create regular datafiles and tempfiles in an operating system file system or to create Automatic Storage Management disk group files. Specify the datafiles to make up the permanent tablespace or the tempfiles to make up the temporary tablespace.
Restrictions on the SYSAUX Tablespace You cannot specify OFFLINE or TEMPORARY for the SYSAUX tablespace.
For guidelines on creating this tablespace, please refer to Oracle Database Upgrade Guide.
Take care to allocate sufficient space for the SYSAUX tablespace. See "DATAFILE | TEMPFILE Clause" for the behavior of the DATAFILE clause. The DATAFILE clause is optional only if you have enabled Oracle-managed files. You must specify EXTENT MANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO for the SYSAUX tablespace. You must have the SYSDBA system privilege to specify this clause, and you must have opened the database in MIGRATE mode. You must use the CREATE TABLESPACE statement to create the SYSAUX tablespace if you are upgrading from a release prior to Oracle Database 10g. Note on the SYSAUX Tablespace SYSAUX is a required auxiliary system tablespace. Specify the name of the tablespace to be created. (Some of these clauses are also used to create a temporary or undo tablespace.) Use the following clauses to create a permanent tablespace. Oracle Database Administrator's Guide for more information on using bigfile tablespaces You cannot specify EXTENT MANAGEMENT DICTIONARY. You can specify only one datafile in the DATAFILE clause or one tempfile in the TEMPFILE clause. Restrictions on Bigfile Tablespaces Bigfile tablespaces are subject to the following restrictions: If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management. If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.Ī 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 22) blocks. This clause overrides any default tablespace type setting for the database.Ī bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion (2 32) blocks. Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. ( file_specification::=, extent_management_clause ::=, tablespace_retention_clause ::=)ĭescription of the illustration tablespace_retention_clause.gif ( file_specification::=, tablespace_group_clause, extent_management_clause ::=)ĭescription of the illustration tablespace_group_clause.gifĭescription of the illustration undo_tablespace_clause.gif See Also: "PERMANENT | TEMPORARY Clauses" for information on these keywords, which were part of the preceding syntax in earlier releasesĭescription of the illustration logging_clause.gifĭescription of the illustration table_compression.gifĭescription of the illustration extent_management_clause.gifĭescription of the illustration segment_management_clause.gifĭescription of the illustration flashback_mode_clause.gifĭescription of the illustration temporary_tablespace_clause.gif You can also drop a tablespace from the database with the DROP TABLESPACE statement. You can subsequently use the ALTER TABLESPACE statement to take the tablespace offline or online, add datafiles or tempfiles to it, or make it a read-only tablespace. When you create a tablespace, it is initially a read/write tablespace. Objects in temporary tablespaces are stored in tempfiles. Oracle strongly recommends that you use automatic undo management mode rather than using rollback segments for undo.Ī temporary tablespace contains schema objects only for the duration of a session. Objects in permanent tablespaces are stored in datafiles.Īn undo tablespace is a type of permanent tablespace used by Oracle Database to manage undo data if you are running your database in automatic undo management mode. Use the CREATE TABLESPACE statement to create a tablespace, which is an allocation of space in the database that can contain schema objects.Ī permanent tablespace contains persistent schema objects.