Table Space Management   «Prev 

Creating Locally Managed SYSTEM Tablespace

Specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement to create a locally managed SYSTEM tablespace. The COMPATIBLE initialization parameter must be set to 9.2 or higher for this statement to be successful. If you do not specify the EXTENT MANAGEMENT LOCAL clause, by default the database creates a dictionary-managed SYSTEM tablespace. Dictionary-managed tablespaces are deprecated.
A locally managed SYSTEM tablespace has AUTOALLOCATE enabled by default, which means that the system determines and controls the number and size of extents. You may notice an increase in the initial size of objects created in a locally managed SYSTEM tablespace because of the autoallocate policy. It is not possible to create a locally managed SYSTEM tablespace and specify UNIFORM extent size. When you create your database with a locally managed SYSTEM tablespace, ensure that the following conditions are met:
  1. A default temporary tablespace must exist, and that tablespace cannot be the SYSTEM tablespace. To meet this condition, you can specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement, or you can omit the clause and let Oracle Database create the tablespace for you using a default name and in a default location.
  2. You can include the UNDO TABLESPACE clause in the CREATE DATABASE statement to create a specific undo tablespace. If you omit that clause, Oracle Database creates a locally managed undo tablespace for you using the default name and in a default location.

Creating Locally Managed Databases

CREATE TABLESPACE tablespacename DATAFILE
  'datafilename' [SIZE n]
 [AUTOEXTEND { OFF | ON }]
 [EXTENT MANAGEMENT 
 {DICTIONARY | LOCAL [AUTOALLOCATE | UNIFORM
  [SIZE]] } ];
To create a locally managed tablespace, Oracle has provided new parameters in the CREATE TABLE command. The EXTENT MANAGEMENT clause can be used in place of the usual storage specifications (such as minimum extents and default storage space.) If you choose DICTIONARY, the tablespace is managed as usual, using the data dictionary. If you choose LOCAL, the tablespace is managed as usual, using the data dictionary. If you choose LOCAL, the tablespace becomes a locally managed tablespace. If you leave out the EXTENT MANAGEMENT clause, the tablespace is by default a normal, dictionary-managed tablespace.

CREATE TABLESPACE localtbsp_01 DATAFILE
  'data_0101.dbs'
 AUTOEXTEND ON
 EXTENT MANAGEMENT 
 LOCAL UNIFORM SIZE 25M;
Here is an example of a locally managed tablespace. The tablespace will be created with uniform extents that are 25 MB in size.

As alternative to specifying uniform extents is to specify the AUTOALLOCATE parameter, as shown in this example. Tell the databse what size the initial extent will be, and the tablespace will decide the best size for all subsequent extents. The minimum extent size allocated this way is 64 K.