How can I create a Locally Managed SYSTEM Tablespace?

When you specify the EXTENT MANAGEMENT LOCAL clause in the CREATE DATABASE statement, you cause Oracle to create a locally managed SYSTEM tablespace wherein Oracle determines extent sizes. 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, the default is to create a dictionary-managed SYSTEM tablespace.

Locally managed tablespaces provide better performance and greater ease of management over dictionary-managed tablespaces. A locally managed SYSTEM tablespace is created AUTOALLOCATE by default, meaning that it is system managed with extent sizes determined and controlled by Oracle. 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 the following conditions are met:

There must be a default temporary tablespace, and that tablespace cannot be the SYSTEM tablespace.

You must not create rollback segments in dictionary-managed tablespaces. Attempting to create a rollback segment in a dictionary-managed tablespace will fail if the SYSTEM tablespace is locally managed.

To meet the first condition, you can specify the DEFAULT TEMPORARY TABLESPACE clause in the CREATE DATABASE statement, or you cannot include the clause and allow Oracle to create the tablespace for you using a default name and in a default location.

For fulfilling the second condition, Oracle recommends that instead of using rollback segments to manage the database’s undo records, that you use automatic undo management. You can include the UNDO TABLESPACE clause in the CREATE DATABASE statement to create a specific undo tablespace, or if you do not include the clause, Oracle creates a locally managed undo tablespace for you using the default name and in a default location.