Wednesday, May 02, 2012

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 DATABASE 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>

No comments: