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:
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.
Creating a bigfile tablespace:
To create a bigfile tablespace, specify the bigfile keyword of the createtablespace statement (createbigfiletablespace …). Oracle database automatically creates a locally managed tablespace with automatic segment space management. You can, but need not, specify extentmanagementlocal and segmentspacemanagementauto in this statement. However, the database returns an error if you specify extentmanagementdictionary or segmentspacemanagementmanual. The remaining syntax of the statement is the same as for the create tablespace statement, but you can only specify one datafile. For example:
Sql>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 createsmallfiletablespace 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:
With a bigfile tablespace, you can use the autoextend clause outside of the adddatafile clause. For example:
Sql>alter tablespace bigtbs autoextend on next 20g;
An error is raised if you specify an adddatafile 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.
Enroll for Instructor Led Live ORACLE DBA TRAINING
Get Updates on Tech posts, Interview & Certification questions and training schedules