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