10G傳輸表空間遇到發行版本問題

SQL> conn / as sysdba
已連線。
SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO YES
         1 UNDOTBS1                       YES NO YES
         2 SYSAUX                         YES NO YES
         4 USERS                          YES NO YES
         3 TEMP                           NO NO YES
         6 EXAMPLE                        YES NO YES
         7 TEST                           YES NO YES

已選擇7行。

SQL> select table_name from dba_tables where tablespace_name='TEST';

TABLE_NAME
------------------------------
REPAIR_TABLE
ORPHAN_KEY_TABLE
EMP3

SQL> select count(*) from scott.emp3;

COUNT(*)
----------
43008

SQL> alter tablespace test read only;

表空間已更改。

C:\Documents and Settings\oracle>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 13:38:09

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-29341: 可傳輸集不是自包含的

作業 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 因致命錯誤於 13:38:16 停止

SQL> EXECUTE dbms_tts.transport_set_check('TEST',TRUE,TRUE);

PL/SQL 過程已成功完成。

SQL> SELECT * FROM transport_set_violations;

VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Partitioned table SCOTT.EMP4$REORG is partially contained in the transportable set: check table partitions by querying s
Default Partition (Table) Tablespace TEST for EMP4$REORG not contained in transportable set
Partitioned table SCOTT.EMP4 is partially contained in the transportable set: check table partitions by querying sys.dba
Default Partition (Table) Tablespace TEST for EMP4 not contained in transportable set
Sys owned object ORPHAN_KEY_TABLE in tablespace TEST not allowed in pluggable set
Sys owned object REPAIR_TABLE in tablespace TEST not allowed in pluggable set
Default Partition (Table) Tablespace EXAMPLE for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4$REORG not contained in transportable set

已選擇8行。

發現表空間有很多與其他表的關聯和表空間關聯.

SQL> select segment_name,partition_name from dba_segments where tablespace_name='TEST';

SEGMENT_NAME                                                                      PARTITION_NAME
--------------------------------------------------------------------------------- -----------------------
EMP3
EMP4$REORG                                                                        EMP4_P2
EMP4                                                                              EMP4_P2
REPAIR_TABLE
ORPHAN_KEY_TABLE

修改分割槽的表空間:

SQL> ALTER TABLE "SCOTT"."EMP4$REORG" MOVE PARTITION "EMP4_P2" TABLESPACE "EXAMPLE" UPDATE INDEXES;

表已更改。

SQL> ALTER TABLE "SCOTT"."EMP4" MOVE PARTITION "EMP4_P2" TABLESPACE "EXAMPLE" UPDATE INDEXES;

表已更改。

SQL> select segment_name,partition_name from dba_segments where tablespace_name='TEST';

SEGMENT_NAME PARTITION_NAME
--------------------------------------------------------------------------------- -----------------------------
EMP3
REPAIR_TABLE
ORPHAN_KEY_TABLE
6.539
6.603

SQL> alter table ORPHAN_KEY_TABLE move tablespace system;

表已更改。

SQL> alter table REPAIR_TABLE move tablespace system;

表已更改。

SQL> EXECUTE dbms_tts.transport_set_check('TEST',TRUE,TRUE);

PL/SQL 過程已成功完成。

SQL> SELECT * FROM transport_set_violations;

VIOLATIONS
----------------------------------------------------------------------------------------------------------------
Default Partition (Table) Tablespace TEST for EMP4$REORG not contained in transportable set
Default Partition (Table) Tablespace TEST for EMP4$REORG not contained in transportable set
Default Partition (Table) Tablespace TEST for EMP4 not contained in transportable set
Default Partition (Table) Tablespace TEST for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4$REORG not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4 not contained in transportable set
Default Partition (Table) Tablespace EXAMPLE for EMP4$REORG not contained in transportable set

已選擇8行。

發現預設屬性也影響匯出,再次修改分割槽的儲存表空間屬性:

SQL> alter table scott.emp4 MODIFY DEFAULT ATTRIBUTES tablespace example;

表已更改。

SQL> alter table scott.emp4$reorg MODIFY DEFAULT ATTRIBUTES tablespace example;

表已更改。

SQL> EXECUTE dbms_tts.transport_set_check('TEST',TRUE,TRUE);

PL/SQL 過程已成功完成。

SQL> SELECT * FROM transport_set_violations;

未選定行

SQL>

D:\>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 14:15:15

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的轉儲檔案集為:
D:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\DPDUMP\TRANSPORT.DMP
作業 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已於 14:15:32 成功完成

拷貝TRANSPORT.DMP和datafile到目標資料庫:

D:\>impdp system/baan dumpfile=IMPDIR:TRANSPORT.DMP transport_datafiles=D:\orac
le\product\10.2.0\oradata\orcl\TEST.DBF

Import: Release 10.2.0.1.0 - Production on 星期四, 15 5月, 2008 14:43:18

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=IMPDIR:TR
ANSPORT.DMP transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-00721: 發行版 10.2.0.4.0 中的更改無法用於發行版 10.2.0.1.0

作業 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命錯誤於 14:43:25 停止

重新指定版本,在源資料庫進行匯出:

D:\>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.2.0.1.0'

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 14:57:34

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=DATA_PUMP_DIR:transport.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.2.0.1.0'
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的轉儲檔案集為:
D:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\DPDUMP\TRANSPORT.DMP
作業 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已於 14:57:41 成功完成

D:\>impdp system/baan dumpfile=IMPDIR:TRANSPORT.DMP transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF

Import: Release 10.2.0.1.0 - Production on 星期四, 15 5月, 2008 15:12:50

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=IMPDIR:TRANSPORT.DMP
transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-00721: 發行版 10.2.0.4.0 中的更改無法用於發行版 10.2.0.1.0

作業 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命錯誤於 15:12:58 停止

降低版本測試一下:

D:\>expdp system/oracle dumpfile=DATA_PUMP_DIR:transport2.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.1.0'

Export: Release 10.2.0.4.0 - Production on 星期四, 15 5月, 2008 15:38:19

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=DATA_PUMP_DIR:transport2.dmp nologfile=y TRANSPORT_TABLESPACES=TEST version='10.1.0'
處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
處理物件型別 TRANSPORTABLE_EXPORT/TABLE
處理物件型別 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
已成功載入/解除安裝了主表 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01"
******************************************************************************
SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 的轉儲檔案集為:
D:\ORACLE\PRODUCT\10.2.0\ADMIN\DEVDB\DPDUMP\TRANSPORT2.DMP
作業 "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" 已於 15:38:27 成功完成

D:\>impdp system/baan dumpfile=IMPDIR:TRANSPORT2.DMP transport_datafiles=D:\ora
cle\product\10.2.0\oradata\orcl\TEST.DBF

Import: Release 10.2.0.1.0 - Production on 星期四, 15 5月, 2008 15:40:25

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功載入/解除安裝了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
啟動 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=IMPDIR:TR
ANSPORT2.DMP transport_datafiles=D:\oracle\product\10.2.0\oradata\orcl\TEST.DBF

處理物件型別 TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: 資料泵可傳輸的表空間作業中止
ORA-00721: 發行版 10.2.0.4.0 中的更改無法用於發行版 10.2.0.1.0

作業 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 因致命錯誤於 15:40:32 停止

難道不能從高版本往低版本傳輸???

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/175005/viewspace-269210/，如需轉載，請註明出處，否則將追究法律責任。

10G傳輸表空間遇到發行版本問題

相關文章