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 bigfile tablespace contains only one data file or temp file, which can contain up to approximately 4 billion (232) blocks. The maximum size of the single data file or temp file is 128 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.(default)

If you omit this clause, then Oracle Database uses the current default tablespace type of permanent or temporary tablespace set for the database. If you specify BIGFILE for a permanent tablespace, then the database by default creates a locally managed tablespace with automatic segment-space management.

Restriction on Bigfile Tablespaces You can specify only one data file in the DATAFILE clause or one temp file in the TEMPFILE 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, 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 data file. A bigfile tablespace with 32K blocks can contain a 128 terabyte data file. The maximum number of data files 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 data files 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 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 you perform operations on tablespaces, rather than the underlying individual data files.

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.

Notes:

  • 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. See your operating system specific documentation for information about maximum supported file sizes.

  • 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 EXTENTMANAGEMENT LOCAL and SEGMENT SPACE MANAGEMENT AUTO in this statement. However, the database returns an error if you specify EXTENTMANAGEMENT DICTIONARY or SEGMENT SPACE MANAGEMENT MANUAL. The remaining syntax of the statement is the same as for theCREATE TABLESPACE statement, but you can only specify one data file. 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 TABLESPACEstatement. 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 aCREATE SMALLFILE TABLESPACE statement to override the default tablespace type for the tablespace that you are 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 its single data file. That number is 1024 on most platforms, but 4096 on OS/390.

==========================

创建BIGFILE 时,oracle 自动创建本地管理和自动段空间管理(ASSM)的表空间.
Oracle Database automatically creates a locally managed tablespace with automatic segment space management.
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 bigfiletablespace containsonly one datafile or tempfile, which can contain up to approximately 4 billion(232) blocks.大文件表空间只能包含一个数据文件或临时文件.
· A smallfiletablespace is atraditional Oracle tablespace, which can contain 1022 datafiles or tempfiles,each of which can contain up to approximately 4 million (222)blocks.
使用大文件表空间的好处:
大文件表空间可以化最小表空间文件数量,从而简化DBA 管理,在大型数据库中的表空间可能包含几十或几百个数据文件,随着文件数据的增加,一些数据库参数需要做出相应的调整比如db_files,这个静态参数还要重启数据库才能生效,这都给数据库的维护带来不便,大文件表空间可以解决这个问题.由于文件数量减少,控制文件的空间使用也随之减少,检查点等数据库内部操作需要同步的文件头数量也大大减少.