Difference between BIGFILE and SMALLFILE tablespace, Advantages. How to Create and Resize Bigfile Tablespace?

advertisements

_____________________________________________________________________________________________________________________

There are 2 kinds of tablespace allowed. One is traditional tablespace which is known as SMALLFILE tablespace and the second one is the BIGFILE tablespace. For bigfile tablespace we should consider with ASM or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.

·        A bigifle tablespace contains only one datafile or tempfile where as smallfile tablespace can contain upto 1022 datafiles or tempfiles.

·        A bigfile tablespace can contain upto 4 billion(2³²) blocks and in one smallfile tablespace can contain upto 2 million(2²²) blocks.

Restriction/Disadvantage:

·        In BIGFILE tablespace you can create only one datafile or tempfile to the tablespace using the respective datafile or tempfile in create tablespace command.

·        The file restoration time would be more in bigfile tablespace.

·        Not supposed to use BIGFILE tablespace, in case if you have the storage space crunch as you cannot add more than one file to the bigfile tablespace.

Advantages:

·        Using the BIGFILE tablespaces you can reduce the total number datafiles to the database. In a smallfile tablespace can contain 1024 datafiles which can be replaced with a single Bigfile datafile.

·        The database performance in terms of database open, checkpoints, DBWR speed would be high in BIGFILE tablespace.

Example for creating a BIGFILE tablespace

CREATE BIGFILE TABLESPACE "APPL_DATA" DATAFILE size 2g AUTOEXTEND ON LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL;

OR

CREATE bigfile TABLESPACE "APPL_DATA" DATAFILE '+DATA'  SIZE 2147483648   AUTOEXTEND ON  ;

_____________________________________________________________________________________________________________________

Website Stats