Thursday, 4 June 2020

What are the features of BIGFILE Tablespace in Oracle Database?

Bigfile Tablespace is introduced from Oracle 10g.A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile.

The benefits of bigfile tablespaces are the following:

A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile 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, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.

Bigfile tablespaces 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 DATABASE and CREATE CONTROLFILE 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 datafiles.

Bigfile tablespaces 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 on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.


Creating a Bigfile Tablespace


To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE TABLESPACE statement (CREATE BIGFILE TABLESPACE ...)

CREATE BIGFILE TABLESPACE bigtbs 
    DATAFILE '/u02/oracle/data/bigtbs01.dbf' SIZE 50G
...


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

No comments:

Post a Comment

Was this Post Helpful?

Feel free to suggest your opinions in the comments section!