Oracle local management comparison data dictionary management tablespace, oracle dictionary

Oracle local management comparison data dictionary management tablespace, oracle dictionary

Locally vs. Dictionary Managed Tablespaces

Organized from: http://www.orafaq.com/node/3.

When Oracleallocates space to a segment (like a table or index), a group of contiguousfree blocks, called an extent, is added to the segment. metadata regardingextent allocation and unallocated extents are either stored in the datadictionary, or in the tablespace itself. tablespaces that record extentallocation in the dictionary, are called dictionary managed tablespaces, andtablespaces that record extent allocation in the tablespace header, are calledlocally managed tablespaces.

Tablespace allocation segment space, that is, zone: A group of continuous blocks. The tablespace information about partition allocation is stored in the data dictionary (DMT) or the tablespace itself (LMT) bitmap area.

View table space management methods in a database:

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

 

TABLESPACE_NAME EXTENT_MAN allow.o

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

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 that is in "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 be obtained to do inserts and deletes agianst these tables. as only one process can acquire the ST enque at a given time, this often leadto contention (competition ).

Use the data dictionary management area to allocate data. Idle space is recorded in the SYS. FET $ table, and used space is recorded in the SYS. UET $ table.

Execute thefollowing statement to create a dictionary managed
Tablespace: create a data dictionary to manage the tablespace:

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50 M

EXTENT MANAGEMENT DICTIONARY

Default storage (INITIAL 50 k next 50 KMINEXTENTS 2 MAXEXTENTS 50 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 Oracle920, the default system tablespace is local management, so you cannot create a tablespace for data dictionary management in the database.
To create a tablespace for data dictionary management, you must change the system tablespace to data dictionary management when creating a database.

SQL> CREATE TABLESPACE ts2 DATAFILE '/oradata/ts2_01.dbf' SIZE 50 M

Extent management local autoallocate;

 

SQL> CREATE TABLESPACE ts3 DATAFILE '/oradata/ts3_01.dbf' SIZE 50 M

Extent management local uniform size 128 K;

Note the differencebetween AUTOALLOCATE and uniform size: Note the difference between AUTOALLOCATE and uniform size options!

AUTOALLOCATEspecifies that extent sizes are system managed. oracle will choose "optimal" next extent sizes starting with 64KB. as the segment growslarger extent sizes will increase to 1 MB, 8 MB, and eventually to 64 MB. this isthe recommended option for a low or unmanaged environment.

UNIFORMspecifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes ). the defaultsize is 1 M. the uniform extent size of a locally managed tablespace cannot beoverridden when a schema object, such as a table or an index, is created.

Also not, if youspecify, LOCAL, you cannot specify default storage, minimum extent orTEMPORARY.

For local tablespace management, the default storage, minimum extent, or TEMPORARY options cannot be specified.

Advantages ofLocally Managed Tablespaces: local management Advantages:

• Eliminates the need for recursive SQL operations against the data dictionary (UET $ and FET $ tables) removes recursive SQL operations on the data dictionary table.
• Reduce contention on data dictionary tables (single ST enqueue) reduces contention for data dictionary tables.
• Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space) does not need to merge idle space on a regular basis.
• Changes to the extent bitmaps do not generate rollback information does not generate rollback information for bitmap Changes.

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 tablespace by default. the following restrictionsapply:

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

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

The data dictionary management tablespace has been deprecated since 920!

Segment SpaceManagement in LMT: Manage segments in a local tablespace

From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managedsegments when setting Segment Space Management to AUTO for a tablespace. Lookat this example: Set automatic Segment Management

SQL> CREATE TABLESPACE ts4 DATAFILE '/oradata/ts4_01.dbf' SIZE 50 M

EXTENT MANAGEMENT LOCAL

Segment space management auto;

Segment SpaceManagement eliminates the need to specify and tune the PCTUSED, FREELISTS, andFREELISTS GROUPS storage parameters for schema objects. the Automatic SegmentSpace Management feature improves the performance of concurrent DML operationssignificantly since different parts of the bitmap can be used simultaneouslyeliminating serialization for free space lookups against the FREELSITS. this isof particle importance when using RAC, or if "buffer busy waits" are deteted.

Convert betweenLMT and DMT: local management and data dictionary management tablespace Conversion

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.

What is the tablespace used to store data dictionaries and system management information in oracle?

System tablespace

Can I manage the ORACLE10 tablespace in a data dictionary?

Yes, but now it's basically no longer needed.
Dictionary management is widely used in 8i.