Bigfile Tablespace in Oracle

A bigfile tablespace in oracle is a tablespace with a single, but very large (up to 4g blocks) datafile. Traditional small file tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of big file tablespaces are the following:

Want To Get DBA Training From Experts? Enroll Now For Free Demo On Oracle DBA Online Certification Training.

• A bigfile tablespace in oracle with 8k blocks can contain a 32 terabyte datafile. A big file 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, big file tablespaces in oracle can significantly enhance the storage capacity of an oracle database.
• Bigfile tablespace in oracle 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 a database and create control file 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 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 to undo tablespaces, temporary tablespaces, and the system tablespace.

Notes:

• Bigfile tablespace are intended to be used with automatic storage management (asm) or other logical volume managers that supports striping or raid, and dynamically extensible logical volumes.
• Avoid creating bigfile tablespaces in oracle on a system that does not support striping because of negative implications for parallel query execution and rman backup parallelization.
• Using bigfile tablespace in oracle 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.

Learn more about Oracle DBA from this Best Oracle DBA Training in Hyderabad to get ahead in your career!

Creating Bigfile Tablespaces:

To create a big file tablespace, specify the bigfile keyword of the create tablespace statement (createbigfiletablespace …). Oracle Database automatically creates a locally managed tablespace with automatic segment space management.

You can, but need not, specify extent management local 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 big file at database creation, you need not specify the keyword big file in the create tablespace statement. A big file tablespace is created by default.

If the default tablespace type was set to big file at database creation, but you want to create a traditional (small file) tablespace, then specify a createsmallfiletablespace statement to override the default tablespace type for the tablespace that you are creating.

[Related Article: Oracle DBA Interview Questions]

Altering a big file tablespace:

Two clauses of the alter tablespace statement support datafile transparency when you are using big file tablespaces:

• Resize: the resize clause lets you resize the single data file in a big file tablespace to absolute size, without referring to the datafile. For example:
• Alter tablespace big tbs resize 80g;
• Auto-extend (used outside of the add datafile clause):

With a bigfile tablespace, you can use the auto-extend clause outside of the add datafile clause. For example:

Sql>alter tablespace bigtbs autoextend on next 20g;

An error is raised if you specify an add datafile clause for a big file tablespace.
Identifying a big file tablespace:

[Related Article: Oracle DBA Tutorial]

The following views contain a big file column that identifies a tablespace as a big file tablespace:

• Dba_tablespaces
• User_tablespaces
• V$tablespace

You can also identify a big file tablespace by the relative file number of its single datafile. That number is 1024 on most platforms, but 4096 on os/390.

 

Explore Oracle DBA Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

 

 

Related Articles: