Bigfile tablespace

From Oracle FAQ
Jump to: navigation, search

A Bigfile Tablespace is a special kind of tablespace than can only have a single (normally very large) datafile. Bigfile tablespaces can be up to 4G blocks that 128 TB with 32k blocks.

Normal tablespaces are referred to as smallfile tablespaces.

Bigfile tablespaces should be used with ASM or with other logical volume managers that support dynamically extensible logical volumes, striping and RAID.

History[edit]

Bigfile tablespaces were introduced in Oracle 10g.

Examples[edit]

Smallfile tablespaces:

create           tablespace x1 datafile '/tmp/x1.dbf' size 1M;
create smallfile tablespace x2 datafile '/tmp/x2.dbf' size 1M;

Bigfile tablespaces:

create bigfile   tablespace x3 datafile '/tmp/x3.dbf' size 1M;

Monitor[edit]

To check if a given tablespace is a bigfile tablespace:

SELECT tablespace_name, bigfile FROM dba_tablespaces;

Backup and restore[edit]

To backup and restore bigfile tablespaces in parallel, specify a "SECTION SIZE" (chunk size to be handled by a single backup piece). This feature was introduced in Oracle 11g. Example:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
RMAN> BACKUP TABLESPACE bigts SECTION SIZE 100m;

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #