Locally vs. Dictionary Managed Tablespaces

整理自：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.

表空間分配段空間，即區(qū)：一組連續(xù)的塊。表空間關(guān)于區(qū)分配的信息被存于數(shù)據(jù)字典（DMT）或表空間自身（LMT）位圖區(qū)

查看數(shù)據(jù)庫中表空間管理方式：

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 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（競爭）.

使用數(shù)據(jù)字典管理區(qū)分配。空暇空間被記錄在SYS.FET$表中，已使用空間記錄在SYS.UET$表。

Execute thefollowing statement to create a dictionary managed
tablespace:? 創(chuàng)建數(shù)據(jù)字典管理表空間：

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

?????EXTENT MANAGEMENT DICTIONARY

?? ???DEFAULT STORAGE ( INITIAL 50K NEXT 50KMINEXTENTS 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:

注意：在Oracle920中，默認(rèn)系統(tǒng)表空間是local管理，因此不能在數(shù)據(jù)庫中建立數(shù)據(jù)字典管理的表空間。
假設(shè)想要建立數(shù)據(jù)字典管理的表空間，必須在建立數(shù)據(jù)庫時，將系統(tǒng)表空間改為數(shù)據(jù)字典管理才干夠。

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: ?注意AUTOALLOCATE與UNIFORM SIZE選項差別！

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 1MB, 8MB, and eventually to 64MB. 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 1M. 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.

假設(shè)是本地管理表空間則不能夠指定DEFAULT STORAGE與MINIMUM EXTENT或TEMPORARY選項。

Advantages ofLocally Managed Tablespaces:?本地管理優(yōu)勢：

• Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables) ?消除對于數(shù)據(jù)字典表的遞歸SQL操作。
• Reduce contention on data dictionary tables (single ST enqueue) ? 降低對數(shù)據(jù)字典表的爭用。
• Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space) ? 不須要定期合并空暇空間。
• Changes to the extent bitmaps do not generate rollback information ? 對于位圖區(qū)的改變不會產(chǎn)生回滾信息。

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.

自920后數(shù)據(jù)字典管理表空間已被廢棄！

Segment SpaceManagement in LMT:?本地管理表空間中的段管理

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:設(shè)置段自己主動管理

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 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 particular importance when using RAC, or if "buffer busy waits"are deteted.

Convert betweenLMT and DMT: 本地管理與數(shù)據(jù)字典管理表空間轉(zhuǎn)換

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.

轉(zhuǎn)載于:https://www.cnblogs.com/mfrbuaa/p/3959335.html

總結(jié)

以上是生活随笔為你收集整理的Oracle本地管理对照数据字典管理表空间的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。