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:

■ 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 supported only for locally managed tablespaces with automatic

segment space management, with three exceptions: locally managed undo tablespaces,temporary tablespaces, and the SYSTEM tablespace.

Notes:

■ 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.

■ Using bigfile tablespaces 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.

Creating a Bigfile Tablespace

To create a bigfile tablespace, specify the BIGFILE keyword of the CREATE

TABLESPACE statement (CREATE BIGFILE TABLESPACE ...). Oracle Database

automatically creates a locally managed tablespace with automatic segment space

management. You can, but need not, specify EXTENT MANAGEMENT LOCAL and

SEGMENT SPACE MANAGEMENT AUTO in this statement. However, the database returns

an error if you specify EXTENT MANAGEMENT DICTIONARY or SEGMENT SPACE

MANAGEMENT MANUAL. The remaining syntax of the statement is the same as for the

CREATE TABLESPACE statement, but you can only specify one datafile. For example:

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 CREATE SMALLFILE

TABLESPACE 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:

■ RESIZE: The RESIZE clause lets you resize the single datafile in a bigfile

tablespace to an absolute size, without referring to the datafile. For example:

ALTER TABLESPACE bigtbs RESIZE 80G;

■ AUTOEXTEND (used outside of the ADD DATAFILE clause):

With a bigfile tablespace, you can use the AUTOEXTEND clause outside of the ADD

DATAFILE clause. For example:

ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

An error is raised if you specify an ADD DATAFILE clause for a bigfile tablespace.

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

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.

oracle bigfile tablespaces

相關文章