More About Bigfile Tablespaces

You can read an introduction to bigfile tablespaces here.

This was tested in an Oracle 10 database. In DATABASE_PROPERTIES, there is a row which determines whether new tablespaces will be SMALLFILE or BIGFILE by default. When you first create a database, this is set to SMALLFILE: 

SQL> l

  1  select property_value, description

  2  from database_properties

  3* where property_name = 'DEFAULT_TBS_TYPE'

SQL> /

 

PROPERTY_VALUE  DESCRIPTION

--------------- -------------------------

SMALLFILE       Default tablespace type

 

SQL>

 

You can change this as follows, if you wish:

 

SQL> alter database set default bigfile tablespace

  2  /

 

Database altered.

 

SQL> select property_value, description

  2  from database_properties

  3  where property_name = 'DEFAULT_TBS_TYPE'

  4  /

 

PROPERTY_VALUE  DESCRIPTION

--------------- -------------------------

BIGFILE         Default tablespace type

 

SQL>

 

Then, if you create a new tablespace, it will be BIGFILE by default:

 

SQL> create tablespace andrew

  2  datafile '/tmp/andrew.dbf' size 10m

  3  /

 

Tablespace created.

 

SQL> select bigfile from dba_tablespaces

  2  where tablespace_name = 'ANDREW'

  3  /

 

BIGFILE

-------

YES

 

SQL>

 

... and, if you want to create a SMALLFILE tablespace, you have to add the SMALLFILE keyword to your CREATE TABLESPACE command:

 

SQL> create smallfile tablespace fred

  2  datafile '/tmp/fred.dbf' size 10m

  3  /

 

Tablespace created.

 

SQL> select bigfile from dba_tablespaces

  2  where tablespace_name = 'FRED'

  3  /

 

BIGFILE

-------

NO

 

SQL>