Oracle Tablespace Bigfile and Smallfile

Bigfile | Smallfile

Use this clause to determine whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database.

• A  , which can contain up to approximately 4 billion ( 232) blocks. The maximum size of the single data file or temp file are terabytes (TB) for a tablespace with 32K blocks and 32TB for A tablespace with 8K blocks.

• A  smallfile Tablespace is a traditional Oracle tablespace , which can contain 1022 data files or temp files, each of which Can contain up to approximately 4 million (222) blocks.

IF you omit this clause and then Oracle Database uses the current default tablespace type of permanent or temporary TABLESPAC E set for the database. If you specify BIGFILE for a permanent tablespace and then the database by default creates a locally managed tablespace with AU Tomatic segment-space Management.

Restriction on Bigfile tablespaces you can specify only one data file in the DATAFILE clause or one temp file in the clause.

Bigfile tablespaces

A bigfile tablespace is a tablespace with a single, but potentially very large (up to 4G blocks) data file. Traditional Smallfile tablespaces, in contrast, can contain multiple data files, and the files cannot be as large. The benefits of Bigfile tablespaces is the following:

• A bigfile tablespace with 8K blocks can contain a + terabyte data file. A bigfile tablespace with 32K blocks can contain a, terabyte data file. The maximum number of data files in a 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 data files needed for a database. An additional benefit was that the DB_FILES initialization parameter and parameter of the and MAXDATAFILES CREATE DATABASE CREATE CONTROLFILE statements can Be adjusted to reduce the amount of SGA space required for data file information and the size of the control file.

• Bigfile tablespaces Simplify database management by providing data file transparency. SQL syntax for the ALTER TABLESPACE statement lets-perform operations on tablespaces, rather than the underlying individual Data files.

Bigfile Tablespaces is supported only for locally managed tablespaces with automatic segment space management, with three Exceptions:locally managed undo tablespaces, Temporary tablespaces, and the SYSTEM tablespace.

Notes:

• Bigfile Tablespaces is intended to being used with Automatic Storage Management (Oracle ASM) or other logical Volume manager s that supports striping or RAID, and dynamically extensible logical volumes.

• Avoid creating Bigfile tablespaces on a system this does not support striping because of negative implications for Paralle L Query execution and RMAN backup parallelization.

• Using Bigfile tablespaces on platforms "do not" support large file sizes are not recommended and can limit tablespace CA Pacity. See your operating system specific documentation for information about maximum supported file sizes.

• Creating a bigfile tablespace

  To create a bigfile tablespace, specify theBIGFILEKeyword of theCREATETABLESPACEStatement (CREATEBIGFILETABLESPACE...).Oracle Database automatically creates a locally managed tablespace with automatic segment space management.Can, but need not, specifyEXTENTMANAGEMENTLOCALandSEGMENTSPACEMANAGEMENTAUTOIn this statement. However, the database returns an error if you specifyEXTENTMANAGEMENTDICTIONARYOrSEGMENTSPACEMANAGEMENTMANUAL. The remaining syntax of the statement are the same as for theCREATE TABLESPACEStatement, but can only specify one data file. For example:

  CREATE bigfile tablespace bigtbs     datafile '/u02/oracle/data/bigtbs01.dbf ' SIZE 50G ...

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

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

  If the default tablespace type BIGFILE is set to at database creation, and you want to create a traditional (smallfile) tab Lespace, then specify a CREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that is Creating.

Identifying a bigfile tablespace

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

• DBA_TABLESPACES

• USER_TABLESPACES

• V$TABLESPACE

You can also identify a bigfile tablespace by the relative file number of the IT single data file. That number is 1024x768 on the most platforms, but 4096 on os/390.

Oracle Tablespace Bigfile and Smallfile