[zt] 如何將資料字典管理表空間(DMT)轉化為本地管理表空間(LMT)

方法1，   alter  table  XXX  move  to  tablespace  XXXX;    然後重建索引， 重建表空間為LMT ,  是用的最多且能徹底消除碎片的方式 。

方法2，   exp/imp方法，可能是非常耗費時間的。

方法3，   使用  DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL可以直接將資料字典管理表空間轉換為本地管理表空間。 對system表空間要慎重，最好不要在產品庫上進行這種操作  (

對於system表空間轉化,需要將資料庫設定成restrict模式 )。   

在很多情況下，如果你想在字典表空間與本地表空間之間轉換是很難的，你可能需要轉出該表空間所有的資料，從新建立表空間，再載入該資料。但是在816以後，有一個叫dbms_space_admin

的包使兩類表空間的互相轉換變成可能，體現在如下兩個過程：
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL

但是在你想利用這個過程進行轉換的時候，你必須注意兩件事：

1、 資料庫版本必須是816以上，相容版本(compatible)必須是8.1以上

2、 如果是轉換成為本地管理，必須有足夠的空閒空間做本地點陣圖空間（8個塊）

當從字典管理到本地管理的過程中，全部轉換其實基本上是不可能發生的，實際情況是，對於已經存在的資料和空間，該過程是沒有任何辦法的，僅僅是簡單把空間取整並標記。所以說，這

種轉換後的表空間可以減緩UET$和FET$的壓力，但並不能解決碎片問題。從查詢DBA_TABLESPACES你還可以看到，轉換之後的表空間管理方式是LOCAL，但實際段分配是USER（不是uniform或

automatic）。

很顯然，在字典管理的表空間中，存在許多大小不同的區間(extent)尺寸，所以轉換為本地管理的時候，ORACLE怎麼樣把這些已經存在的空間轉換為通用大小了？為了做到這一點，ORACLE必

須掃描該表空間的每個資料檔案，主要是檢查以下三個問題：

1、 所有的已經存在的區間

2、 所有的以前用過，但是現在空閒的空間

3、 由表空間MINIMUM EXTENT語句標記的大小

在轉換的時候，ORACLE試圖發現一個適合於以上三個標準的最大的區間的尺寸作為本地管理的區間尺寸，也就是說，在最壞的情況下，這個最大的區間可能就是單個塊（如果說一個表的區間

尺寸是7個塊，另外一個表的區間尺寸是8個塊）

我們看一個從字典管理表空間到本地管理表空間的例子

1、首先，我們建立一個字典管理表空間SQL> create tablespace blah
datafile 'G:ORA9IORADATADB9BLAH.DBF' size 10m reuse
extent management dictionary;
Tablespace altered.

SQL> col bytes format 999,999,999
SQL> select * from dba_free_space where tablespace_name = 'BLAH';

TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCK RELATIVE_FNO
--------------- -------- ----------- ------------ ------- ----------------
BLAH 8 2 10,477,568 1279 8
2、我們在上面建立三個表，最小公用尺寸是400KSQL> create table t1 ( x number ) storage ( initial 400k) tablespace blah;
Table created.

SQL> create table t2 ( x number ) storage ( initial 800k) tablespace blah;
Table created.

SQL> create table t3 ( x number ) storage ( initial 1200k) tablespace blah;
Table created.

SQL> select * from dba_free_space where tablespace_name = 'BLAH';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCK RELATIVE_FNO
--------------- -------- ----------- ----------- ------- ----------------
BLAH 8 302 8,019,968 979 8

SQL> select bytes from dba_extents where tablespace_name = 'BLAH';
BYTES
----------
409,600
819,200
1,228,800
3、現在我們開始轉換該表空間為本地管理的表空間，假定每個點陣圖大小400K，也就是50個塊。SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50);
BEGIN dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50); END;

*
ERROR at line 1:
ORA-03241: Invalid unit size
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

如果我們設定表空間的minimum extent語句為400K： SQL> alter tablespace blah minimum extent 400k;
Tablespace altered.

SQL> exec dbms_space_admin.TABLESPACE_MIGRATE_TO_LOCAL('BLAH',50);
PL/SQL procedure successfully completed.
Conversion goes through with no problems.

從以上可以看到，轉換成功，但實際情況遠遠比這麼複雜，或許你根本就不知道表空間裡面的公用尺寸是多大。而且透過這種轉換後的表空間，並沒有消除碎片，也不一定有最佳化的作用。所

以建議不要用該方法進行轉換，而是使用alter table move的辦法進行表空間的轉換將可能是最好的辦法。

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

修改系統表空間(SYSTEM)從DMT到LMT

 

測試環境測試過程：

SQL>  Exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;

*

ERROR at line 1:

ORA-10643: Database should be mounted in restricted mode and Exclusive mode

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0

ORA-06512: at line 1

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup restrict;

ORACLE instance started.

Total System Global Area 1269796704 bytes

Fixed Size                   733024 bytes

Variable Size            1090519040 bytes

Database Buffers          167772160 bytes

Redo Buffers               10772480 bytes

Database mounted.

Database opened.

 

SQL> Exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

BEGIN dbms_space_admin.tablespace_migrate_to_local('SYSTEM'); END;

 

*

ERROR at line 1:

ORA-10647: Tablespace other than SYSTEM,APPS_UNDOTS1, TEMP not found in read

only mode

ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0

ORA-06512: at line 1

 

ORA-30021: Operation not allowed on undo tablespace

 

select 'alter tablespace  '||tablespace_name|| '  read only ;',status from dba_tablespaces;

 

SQL> alter tablespace PERFSTAT read only;

Tablespace altered.

SQL> alter tablespace OEM_REPOSITORY read only;

Tablespace altered.

SQL> alter tablespace PORTAL read only;

Tablespace altered.

SQL> alter tablespace OWAPUB read only;

Tablespace altered.

SQL> alter tablespace OLAP read only;

Tablespace altered.

SQL>  alter tablespace ODM read only;

Tablespace altered.

SQL> alter tablespace  CTXD  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_TX_IDX  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_TX_DATA  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_SUMMARY  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_SEED  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_QUEUES  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_NOLOGGING  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_MEDIA  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_INTERFACE  read only ;

Tablespace altered.

SQL> alter tablespace  APPS_TS_ARCHIVE  read only ;

Tablespace altered.

 

SQL>  Exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

PL/SQL procedure successfully completed.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup

ORACLE instance started.

Total System Global Area 1269796704 bytes

Fixed Size                   733024 bytes

Variable Size            1090519040 bytes

Database Buffers          167772160 bytes

Redo Buffers               10772480 bytes

Database mounted.

Database opened.

 

select 'alter tablespace  '||tablespace_name|| '  read write ;',status from dba_tablespaces;

 

SQL> alter tablespace  PERFSTAT  read write ;

SQL> alter tablespace  APPS_TS_ARCHIVE  read write ;  

SQL>alter tablespace  APPS_TS_INTERFACE  read write ;     

SQL>alter tablespace  APPS_TS_MEDIA  read write ; 

SQL>alter tablespace  APPS_TS_NOLOGGING  read write ;     

SQL>alter tablespace  APPS_TS_QUEUES  read write ;

SQL>alter tablespace  APPS_TS_SEED  read write ;  

SQL> alter tablespace  APPS_TS_SUMMARY  read write ;  

SQL>alter tablespace  APPS_TS_TX_DATA  read write ;

SQL>alter tablespace  APPS_TS_TX_IDX  read write ;

SQL> alter tablespace  CTXD  read write ;     

SQL>alter tablespace  ODM  read write ;   

SQL>alter tablespace  OLAP  read write ;  

SQL>alter tablespace  OWAPUB  read write ;

SQL>alter tablespace  PORTAL  read write ; 

SQL> alter tablespace  OEM_REPOSITORY  read write ;