Oracle Big File Tablespace Restrictions

• You use bigfile tablespaces along with a Logical Volume Manager (LVM) or the Automatic Storage Management (ASM) feature, which support striping and mirroring.

• Both parallel query execution and RMAN backup parallelization would be adversely impacted, if you used bigfile tablespaces without striping.

• You cannot change tablespace type from smallfile to bigfile or vice versa. However, you can migrate object between tablespace types by using either the ALTER TABLE ... MOVE or CREATE TABLE ... AS

• To avoid performance implications, use the following table as a guide to the maximum number of extents for a BFT with specific block size. If the expected size requires more extents than specified in the table, you can create the tablespace with UNIFORM option (instead of AUTOALLOCATE) with a large extend size.

Once you set the default type of your tablespace, all the tablespaces you subsequently create will be by default of the bigfile type:

CREATE DATABASE test
SET DEFAULT BIGFILE TABLESPACE ... ;
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

You can view the default tablespace type using the following command:

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME='DEFAULT_TBS_TYPE'

CREATE BIGFILE TABLESPACE bigtbs
DATAFILE '/u01/oracle/data/bigtbs_01.dbf' SIZE 100G ...

When you use the BIGFILE clause, Oracle will automatically create a locally managed tablespace with automatic segment-space management (ASSM).

You can use the keyword SMALLFILE in replacement with BIGFILE clause.

ALTER TABLESPACE bigtbs RESIZE 120G;
ALTER TABLESPACE bigtbs AUTOEXTEND ON NEXT 20G;

All the following views have the new YES/NO column

BIGFILE:

o DBA_TABLESPACES
o USER_TABLESPACES
o V$TABLESPACE

For bigfile tablespaces, there is only a single file, with the relative file number always set to 1024.

The only supported way to extract the ROWID components is by using the DBMS_ROWID package.

You can specify the tablespace type by using the new parameter TS_TYPE_IN, which can take the values BIGFILE and SMALLFILE.

SELECT DISTINCT DBMS_ROWID.ROWID_RELATIVE_FNO
(rowid,'BIGFILE ') FROM test_rowid

Note: The functions DATA_BLOCK_ADDRESS_FILE and DATA_BLOCK_ADDRESS_BLOCK in the package DBMS_UTILITY do not return the expected results with BFTs.

You cannot run multiple instances of DBVERIFY utility in parallel against BFT. However, integrity-checking parallelism can be achieved with BFTs by starting multiple instances of DBVERIFY on parts of the single large file. In this case, you have to explicitly specify the starting and ending block addresses for each instance.

dbv FILE=BFile1 START=1 END=10000
dbv FILE=BFile1 START=10001

You can obtain detailed information about the segments in each tablespace using Enterprise Manager.