尝试将本地管理的表空间转换为字典管理表空间

1. 问题及环境

由于做一个测试需要用到字典管理的表空间,尝试在自己的测试库上建一个
jcq0>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

2. 小试牛刀

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 dictionary managed 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 temporary tablespaces and migration of SYSTEM tablespaces are not supported.

5.结论

 如果你想在SYSTEM为本地管理表空间的数据库上建立字典管理的表空间,那么答案是 no way!!!

(需要引用, 请注明出处:痴情甲骨文http://space.itpub.net/14130873)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14130873/viewspace-530051/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14130873/viewspace-530051/