ORA-12913: 無法建立字典管理的資料表空間

jcq0>show user
USER 為"SYS"
jcq0>create tablespace dictionary_t
  2  datafile 'D:/ORACLE/ORADATA/DB_NAMEX/dict.dbf' size 50M
  3  extent management dictionary
  4  default storage(initial 100k next 100k);
create tablespace dictionary_t
*
ERROR 位於第 1 行:
ORA-12913: 無法建立字典管理的資料表空間

 

查看錯誤解釋

ORA-12913 Cannot create dictionary managed tablespace

Cause: An attempt was made to create a dictionary managed tablespace in a database whose SYSTEM tablespace is locally managed.

Action: Create a locally managed tablespace.

該資訊告訴我們只能在SYSTEM資料表空間為字典管理時才能用以上語句建立字典管理的資料表空間

3.峰迴路轉

突然想到不是有個包可以在本地管理的資料表空間和字典管理的資料表空間之間遷移麼,之後找到該包

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL

看來果然可以,於是馬上動手

jcq0>execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('TEST10');
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('TEST10'); END;

*
ERROR 位於第 1 行:
ORA-12914: 無法將資料表空間移植到字典管理的類型中
ORA-06512: 在"SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: 在line 1

ORA-12914 Cannot migrate tablespace to dictionary managed type

Cause: An attempt was made to migrate a locally managed tablespace to dictionary managed type when the database has a locally managed SYSTEM tablespace.

Action: Such an operation cannot be issued.

同樣的提示,所以先遷移SYSTEM資料表空間

jcq0>execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('SYSTEM');
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('SYSTEM'); END;

*
ERROR 位於第 1 行:
ORA-03251: 無法在 SYSTEM 資料表空間上提交此命令
ORA-06512: 在"SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: 在line 1

ORA-03251 Cannot issue this command on SYSTEM tablespace

Cause: It is not permitted to migrate SYSTEM tablespace from locally managed format to dictionarymanaged format or relocate bitmaps.

Action: Check the tablespace name and procedure name.

4.回到原點

翻到doc對該過程的說明可以得到同樣的解釋

The tablespace must be kept online and read/write during migration. Migration of temporarytablespaces and migration of SYSTEM tablespaces are not supported.

5.結論

 如果你想在SYSTEM為本地管理資料表空間的資料庫上建立字典管理的資料表空間,那麼答案是 no way!!!