傳輸表空間及問題處理

今天試驗表空間傳輸，從一個庫PROD 傳輸表空間到repos

 

假定表employees 在tbs4表空間裡

SQL> select table_name,tablespace_name from user_tables where tablespace_name='TBS4';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEES                      TBS4

當然employees 表裡有一些constaints什麼的。

表空間傳輸，可以用dbms_tts.transport_set_check('TBS4',TRUE)；

然後用transport_set_violations來查詢傳輸是否有可行。

 

發現有一些關聯的constraint（外來鍵等)的影響

 

spool 如下指令碼

SQL> select 'alter table employees disable constraint '||constraint_name ||' CASCADE;' from user_constraints where table_name='EMPLOYEES';

'ALTERTABLEEMPLOYEESDISABLECONSTRAINT'||CONSTRAINT_NAME||'CASCADE;'
--------------------------------------------------------------------------------
alter table employees disable constraint EMP_LAST_NAME_NN CASCADE;
alter table employees disable constraint EMP_EMAIL_NN CASCADE;
alter table employees disable constraint EMP_HIRE_DATE_NN CASCADE;
alter table employees disable constraint EMP_JOB_NN CASCADE;
alter table employees disable constraint EMP_SALARY_MIN CASCADE;
alter table employees disable constraint EMP_EMAIL_UK CASCADE;
alter table employees disable constraint EMP_EMP_ID_PK CASCADE;
alter table employees disable constraint EMP_MANAGER_FK CASCADE;
alter table employees disable constraint EMP_JOB_FK CASCADE;
alter table employees disable constraint EMP_DEPT_FK CASCADE;
alter table employees disable constraint SYS_C003327 CASCADE;

'ALTERTABLEEMPLOYEESDISABLECONSTRAINT'||CONSTRAINT_NAME||'CASCADE;'
--------------------------------------------------------------------------------
alter table employees disable constraint SYS_C003328 CASCADE;
alter table employees disable constraint SYS_C003329 CASCADE;

13 rows selected.
執行就可以去除constraint的問題了。

檢查沒問題就可以傳輸了。

 

首先把tbs4置為只讀狀態

alter tablespace tbs4 read only;

 

exp \'sys/oracle@PROD as sysdba\' file=hr_PROD.dmp transport_tablespace=y

tablespaces=tbs4

然後scp到目標庫的機器上，資料檔案tbs04.db也複製到對應的目錄下

在目標庫上執行

imp \'sys/oracle123@repos as sysdba\' file=hr_PROD.dmp transport_tablespace=y tablespaces=tbs4 datafiles=/u01/app/db/oradata/repos/tbs04.dbf

 

執行過後，似乎一切都ok了，新建一個分割槽表，結構和employees一模一樣。

 

SQL> create table emp_part partition by range(hire_date)
(
PARTITION p1 VALUES LESS THAN(TO_DATE('01/01/1980','DD/MM/YYYY')) tablespace tbs1,
PARTITION p2 VALUES LESS THAN(TO_DATE('01/01/1990','DD/MM/YYYY')) tablespace tbs2,
PARTITION p3 VALUES LESS THAN(TO_DATE('01/01/2000','DD/MM/YYYY')) tablespace tbs3,
PARTITION p4 VALUES LESS THAN(TO_DATE('01/01/2010','DD/MM/YYYY')) tablespace tbs4,
PARTITION p5 VALUES LESS THAN(maxvalue) tablespace tbs1
)
as select * from employees where rownum<1;  2    3    4    5    6    7    8    9 
as select * from employees where rownum<1
                 *
ERROR at line 9:
ORA-28110: policy function or package HR.RLS_F has error

這個表中可能還有原來的函式

 

--檢查一下是否存在

 

SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE='FUNCTION' AND OBJECT_NAME ='RLS_F';

OBJECT_NAME
--------------------------------------------------------------------------------
RLS_F

 

--重新編譯一下就可以了。

SQL> alter function HR.RLS_F compile;

Function altered.

最後一定記住把傳輸後的表空間置為read write.