Migrating Dictionary Managed Tablespace To Locally Managed Tablespace

 Migrate a dictionary managed SYSTEM tablespace to locally managed :

SQL> DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL          ('SYSTEM');

Use the below following steps to convert your SYSTEM tablespace from Dictionary Managed to locally Managed :

• Make a complete backup of your Database
• Ensure that the database has a default temporary tablespace that is not SYSTEM. The temporary tablespace is created using the CREATE TEMPORARY TABLESPACE command.
• Eliminate any undo (rollback) segments in disctionary-managed tablespaces.
• There should be at least one online undo segment in a locally managed tablespace, or an undo tablespace should be online.
• All tablespaces other than the tablespace containing the undo space and the default temporary tablespace should be placed in READ ONLY mode.
• Startup the instance in restricted mode.
• Migrate the SYSTEM tablespace by using the above command.

Note: Any non-SYSTEM dictionary managed tablespace that is not migrated to locally managed prior to migrating the SYSTEM tablespace, cannot be altered to READ-WRITE. The tablespace will remain READ-ONLY. Only locally managed tablespaces can be altered to READ-WRITE after the migration of the system tablespace.

Thank you !!

Space Management in Tablespace

Tablespaces allocated space in extents. Tablespaces can be created to use one of the following two different methods of keeping track of free and used space.

• Locally Managed tablespace
• Dictionary Managed Tablespace

Locally Managed tablespace:

- Free Extents are managed in the tablespace.

- Bitmap is used to records free extents.

- Each bit corressponds to a block or group of blocks.

- Bit Value indicates free or used.

The Extents are managed within the tablespace via bitmaps. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, the oracle server changes the bitmap values to show the new status of the bocks. Locally managed is the default tablespace management it is introduced in Oracle 9i. 

Locally managed tablespaces are :

• Reduced contention on data dictionary tables.
• No undo generated when space allocation or deallocation occurs
• No Coalescing required.

SQL Command for Creation of locally managed tablespace for Example:

SQL> CREATE TABLESPACE userdata 

DATAFILE '/u01/oradata/userdata01.dbf' size 100M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; 

The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespace is to be locally managed. By default, a tablespace is locally managed.   

extent_management_clause :

 [EXTENT MANAGEMENT [ DISCTIONARY | LOCAL 

[AUTOALLOCATE | UNIFORM [SIZE integer [K/M]]]]

Where : 

DICTIONARY: Specifies that the tablespace is managed using dictionary tables.

LOCAL: Specifies that the tablespace is Locally managed via bitmaps. If you specify LOCAL, you cannot specify DEFAULT  storage_caluse, MINIMUM EXTENT, or TEMPORARY.

AUTOALLOCATE: Specifies that the tablespace is system managed. Users cannot specify the extent size. This is the default.

UNIFORM: Specifies that the tablespace is managed with uniform extents of size bytes. Use the K or M to specify the extent size in kilobytes or megabytes. The default size is 1 MB. 

The EXTENT_MANAGEMENT clause can be used in various CREATE command :

• For a permanent tablespace, you can specify EXTENT_MANAGEMENT_LOCAL in the CREATE TABLESPACE command. 

Note : Parior to Oracle 9i release 2 , the SYSTEM tablespace was not LOCALLY managed.

• For a temporary tablespace, you can specify EXTENT_MANAGEMENT_LOCAL in the CREATE TEMPORARY TABLESPACE  command.

 

Advantages of Locally Managed Tablespaces :

A locally managed tablespace has the following advantage  over the Dictionary Managed tablespaces :

• Local Management avoids recursive space management operations. This can occur in Dictionary-Managed tablespaces if consuming or releasing space to an extent and in results of this operation another operation that consumes or release space in an Undo segment or Data Dictionary tables.
• Because locally managed tablespaces do not record free space in the data dictionary tables, they reduce contention on these tables.
• Local management of extents automatically tracks adjacent free space, Eliminating the need to coalesce free extents.
• The sizes of the extents that are managed locally can be determined automatically by the system.
• Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information). 

   

Dictionary- Managed Tablespace: Characteristics : 

- Free Extents are managed by the database disctionary.

- Extents are managed in the data dictionary.

- Appropriate tables are updated when extents are allocated or deallocated.

- Each segment stored in the tablespace can have a different storage clause.

- Coalescing is required.

The extents are managed by the data dictionary. The Oracle server updates the appropriate tables in the data dictionary whenever an extent is allocated or deallocated.

Segments in dictionary-managed tablespaces can have customized storage. This storage is more flexible than locally managed tablespaces but much less efficient.

SQL Command for Creation of Dictionary managed tablespace for Example:

SQL> CREATE TABLESPACE userdata

 DATAFILE  '/u01/oradata/userdata01.dbf' size 100M 

 EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE 

 (initial 1M next 1M PCTINCREASE 0);

 

Thank you !!