Oracle Local management vs. data dictionary management table space

Locally vs. Dictionary Managed tablespaces

organized from :HTTP://WWW.ORAFAQ.COM/NODE/3.

When Oracleallocates space to a segment (as a table or index), a group of contiguousfree blocks, called an extent, is ad Ded to the segment. Metadata regardingextent allocation and unallocated extents is either stored in the datadictionary, or in the tablespace itself. Tablespaces that record extentallocation in the dictionary, is called Dictionary managed tablespaces, andtablespaces that Record extent allocation in the Tablespace header, is calledlocally managed tablespaces.

Tablespace allocation segment space, or area: a contiguous set of blocks. Tablespace information about area allocations is stored in the data dictionary (DMT) or tablespace itself (LMT) bitmap area

To view the table space management methods in the database:

Sql> Select Tablespace_name,extent_management, allocation_type from Dba_tablespaces;

Tablespace_name Extent_man Allocatio

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

SYSTEM DICTIONARY USER

Sys_undots LOCAL SYSTEM

TEMP LOCAL UNIFORM

Dictionarymanaged tablespaces (DMT):

Oracle use Thedata dictionary (tables in the SYS schema) to track allocated and free extentsfor tablespaces "Dictionary managed" mode. Free space isrecorded in the SYS. fet$ table, and used space in the SYS. uet$ table. Wheneverspace is required in one of these tablespaces, the ST (space transaction) Enqueue latch must are obtained to do Inse RTS and deletes agianst these tables. As only one process can acquire the ST Enque at a given time, this often leadto contention(competition).

assign using the data Dictionary admin area. The free space is recorded in the SYS. In the fet$ table, the spatial record is used in the SYS. uet$ table.

Execute thefollowing statement to create a dictionary managed
Tablespace: Create a data dictionary management table space:

sql> CREATE tablespace ts1 datafile '/oradata/ts1_01.dbf ' SIZE 50M

EXTENT MANAGEMENT DICTIONARY

DEFAULT STORAGE (INITIAL 50K NEXT 50KMINEXTENTS 2 MAXEXTENTS pctincrease 0);

Locally managedtablespaces (LMT):

Using LMT, Eachtablespace manages it ' s own free and used space within a bitmap structurestored in one of the tablespace ' s Data files. Each bit corresponds to Adatabase block or group of blocks. Execute one of the following statements tocreate a locally managed
Tablespace:

Note: in the Oracle920 , the default system table space is Local Management, you cannot establish a table space for data dictionary management in the database.
If you want to establish a table space for data dictionary management, you must change the system table space to data dictionary management when you build the database.

sql> CREATE tablespace ts2 datafile '/oradata/ts2_01.dbf ' SIZE 50M

EXTENT MANAGEMENT LOCAL autoallocate;

sql> CREATE tablespace ts3 datafile '/oradata/ts3_01.dbf ' SIZE 50M

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Note the Differencebetween autoallocate and UNIFORM SIZE: Note autoallocate with the UNIFORM SIZE option Difference!

Autoallocatespecifies that extent sizes is system managed. Oracle would choose "optimal" next extent sizes starting with 64KB. As the segment Growslarger extent sizes would increase to 1MB, 8MB, and eventually to 64MB. This isthe recommended option for a low or unmanaged environment.

Uniformspecifies that the tablespace are managed with uniform extents of size bytes (use K or M to specify the extent SIZE I n kilobytes or megabytes). The DefaultSize is 1M. The uniform extent size of a locally managed tablespace cannot beoverridden when a schema object, such as a table or an in Dex, is created.

Also not, if youspecify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT ortemporary.

If you are managing a table space locally, you cannot specify DEFAULT STORAGE with the MINIMUM EXTENT or Temporary options.

advantages oflocally Managed tablespaces: Local management benefits:

• Eliminates the need for recursive SQL operations against the data dictionary (uet$ and fet$ tables) eliminates recursive SQL operations for data dictionary tables.
• The reduce contention on data dictionary tables (single ST enqueue) reduces contention on the table.
• Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free spa CE) does not require regular consolidation of free space.
• Changes to the extent bitmaps does not generate rollback information changes to the bitmap area will not produce rollback information.

Locally Managedsystem tablespace:

From Oracle9irelease 9.2 One can change the SYSTEM tablespace to locally managed. Further, ifyou create a database with DBCA (Database Configuration Assistant), it willhave a locally managed SYSTEM tables Pace by default. The following restrictionsapply:

• No dictionary-managed tablespace in the database can be READ WRITE.
• You cannot create new dictionary managed tablespaces
• Cannot convert any dictionary managed tablespaces to local

Thus, it's bestonly to convert the SYSTEM tablespace to LMT after
All other tablespaces is migrated to LMT.

from 920 After the Data Dictionary management table space has been discarded!

Segment spacemanagement in LMT: Manage segment Management in a local management table space

From Oracle 9i,one can not only has bitmap managed tablespaces, but also bitmap managedsegments when setting Segment Space Management to AUTO for a tablespace. Lookat This example: set up segment auto-management

sql> CREATE tablespace ts4 datafile '/oradata/ts4_01.dbf ' SIZE 50M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

Segment Spacemanagement eliminates the need to specify and tune the pctused, freelists, andfreelists GROUPS storage parame Ters for schema objects. The Automatic segmentspace Management feature improves the performance of concurrent DML operationssignificantly since dif Ferent parts of the bitmap can be used simultaneouslyeliminating serialization for free space lookups against the Freelsit S. This isof particular importance when using RAC, or if "buffer busy waits" is deteted.

Convert BETWEENLMT and DMT: Local Administration and Data Dictionary management table space transformations

Thedbms_space_admin package allows DBAs to quickly and easily
Convert between LMT and DMT mode. Look at these examples:

Sql> exec dbms_space_admin. Tablespace_migrate_to_local (' ts1 ');

PL/SQL procedure successfully completed.

Sql> Execdbms_space_admin. Tablespace_migrate_from_local (' ts2 ');

PL/SQL procedure successfully completed.

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

Dylan presents.

Oracle Local Management vs. data dictionary management tablespace