oracle11g_基表I_DEPENDENCY1之move tablespace移动表空间到非system_

老外的贴子,关于在不同版本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/