老外的贴子,关于在不同版本oracle移动基表自system表空间到非system表空间一些研究
http://www.ora600.be/ora600-blog
/**************我的测试****************/
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
Database opened.
/******如下索引是system表空间的索引对象********/
SQL> select object_name from user_objects where object_name='I_DEPENDENCY1';
OBJECT_NAME
--------------------------------------------------------------------------------
I_DEPENDENCY1
SQL> select object_name,object_type from user_objects where object_name='I_DEPEN
DENCY1';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
--------------------------------------
I_DEPENDENCY1
INDEX
/*******上述索引为组合索引,基于基表dependency$
SQL> select table_name,column_name from user_ind_columns where index_name='I_DEP
ENDENCY1';
TABLE_NAME
------------------------------------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DEPENDENCY$
D_OBJ#
DEPENDENCY$
D_TIMESTAMP
DEPENDENCY$
ORDER#
SQL> select tablespace_name from user_indexes where index_name='I_DEPENDENCY1';
TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
SQL> alter index I_DEPENDENCY1 rebuild online tablespace tbs_16k;
Index altered.
/***从现在的实验看基对象可以移动表空间****/
SQL> select tablespace_name from user_indexes where index_name='I_DEPENDENCY1';
TABLESPACE_NAME
------------------------------------------------------------
TBS_16K
/******数据库重启也是正常的*********/
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 238034944 bytes
Fixed Size 2174520 bytes
Variable Size 159384008 bytes
Database Buffers 71303168 bytes
Redo Buffers 5173248 bytes
Database mounted.
Database opened.
SQL> select tablespace_name from user_indexes where index_name='I_DEPENDENCY1';
TABLESPACE_NAME
------------------------------------------------------------
TBS_16K
/*****建立主外键约束也正常*********/
SQL> conn scott/system
Connected.
SQL> create table t_f(a int primary key,b int);
Table created.
SQL> create table t_c(aa int primary key,a int);
Table created.
SQL> alter table t_c add constraint fk_a foreign key(a) references t_f(a);
Table altered.
/********看下基表的定义***********/
SQL> desc DEPENDENCY$;
Name Null? Type
----------------------------------------- -------- --------------
D_OBJ# NOT NULL NUMBER
D_TIMESTAMP NOT NULL DATE
ORDER# NOT NULL NUMBER
P_OBJ# NOT NULL NUMBER
P_TIMESTAMP NOT NULL DATE
D_OWNER# NUMBER
PROPERTY NOT NULL NUMBER
D_ATTRS RAW(2000)
D_REASON RAW(2000)
SQL> select d_obj#,order#,p_obj# from DEPENDENCY$ where rownum=1;
D_OBJ# ORDER# P_OBJ#
---------- ---------- ----------
117 0 116
SQL> select object_name,object_id from dba_objects where object_name='T_F';
OBJECT_NAME
----------------------------------------------------------------------------
OBJECT_ID
----------
T_F
70284
/*****DEPENDENCY$基表其实就是存储相关依赖对象的表/
SQL> select owner,object_type,object_name from dba_objects where object_id in (select d_obj# from DEPENDENCY$) and wner='SCOTT';
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- --------------------------------------------------------------------------------
SCOTT PROCEDURE PROC_INNER
SCOTT PROCEDURE PROC_OUTER
SCOTT PROCEDURE PROC_XML
SCOTT VIEW V_T_TEST
SCOTT PROCEDURE PROC_DBA
SCOTT PROCEDURE PROC_ARG
SCOTT PROCEDURE PROC_SEP
SCOTT MATERIALIZED VIEW MV_PROC_SEP
SCOTT PROCEDURE PROC_OY
SCOTT VIEW V_EDITION
SCOTT MATERIALIZED VIEW MV_T
SCOTT PROCEDURE PROC_LIKE
SCOTT PROCEDURE PROC_DATABASE_LINK
SCOTT PROCEDURE PROC_T_SQL
SCOTT PROCEDURE PROC_TRACK
SCOTT PROCEDURE PROC_NOT_LIKE
SCOTT PROCEDURE PROC_DB_LINK
SCOTT PROCEDURE PROC_FILTER
SCOTT PACKAGE PACK_ORDER_DATE
SCOTT PACKAGE BODY PACK_ORDER_DATE
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- --------------------------------------------------------------------------------
SCOTT VIEW V_T_LIMIT
21 rows selected
/********通过这个DEPENDENCY$可以查询整个数据库的对象依赖关系很不错,比如:在调试复杂的多层存储过程时,可以用此列出层级关系,针对性分析****/
/****基表的索引可以移动表空间,基表DEPENDENCY$可以移动表空间吗/
SQL> conn scott/system
Connected.
SQL> create table t_sr(a int);
Table created.
/**********错误终于出现了,建立基于上述表的存储过程出事了**********/
SQL> create or replace procedure proc_depe
2 as
3 begin
4 select count(a) into v_cnt from t_sr;
5 end;
6 /
create or replace procedure proc_depe
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'SYS.I_DEPENDENCY1' or partition of such index is in unusable
state
/****提示基表索引不可用****我们重建下如何**********/
SQL> select owner,index_name,status from dba_indexes where index_name='I_DEPENDE
NCY1';
OWNER
------------------------------------------------------------
INDEX_NAME STATUS
------------------------------------------------------------ ----------------
SYS
I_DEPENDENCY1 UNUSABLE
SQL> conn sys/sysstem as sysdba
Connected.
SQL> alter index I_DEPENDENCY1 rebuild online;
Index altered.
/*******重构索引**********/
SQL> select owner,index_name,status from dba_indexes where index_name='I_DEPENDE
NCY1';
OWNER
------------------------------------------------------------
INDEX_NAME STATUS
------------------------------------------------------------ ----------------
SYS
I_DEPENDENCY1 VALID
/*************重构后重建了出错的存储过程ok了*****************/
SQL> conn scott/system
Connected.
SQL> create or replace procedure proc_depe
2 as
3 v_cnt pls_integer;
4 begin
5 select count(a) into v_cnt from t_sr;
6 end;
7 /
Procedure created.
小结:1,oracle11g r2可以移动system表空间的基表及基表对象到非system表空间
2,迁移表空间基表索引要重构
总结:1,上面的小结
2,可以用11g打开10g库.注意添加compatible为10g
3,打开后10g与11g字典基表定义不同,用10046分析少了什么
4,手工添加少的或删除多的列
5,重构无效索引或基表
6,@?\rdbms\admin\utlrp.sql重编译无效对象
7,查询是否还有无效对象,如有,重复6
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-757176/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-757176/