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
Copyright (c) 2003, 2007, Oracle. All rights reserved.
連線到: 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
Copyright (c) 2003, 2007, Oracle. All rights reserved.
連線到: 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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
連線到: 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
Copyright (c) 2003, 2007, Oracle. All rights reserved.
連線到: 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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
連線到: 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
Copyright (c) 2003, 2007, Oracle. All rights reserved.
連線到: 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
Copyright (c) 2003, 2005, Oracle. All rights reserved.
連線到: 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 傳輸表空間及問題處理
- oracle跨版本與平臺執行傳輸表空間Oracle
- 10g跨平臺傳輸表空間
- oracle 10g 傳輸表空間的測試Oracle 10g
- Oracle 10g的可傳輸表空間操作Oracle 10g
- MySQL 傳輸表空間MySql
- Oracle 表空間傳輸Oracle
- oracle表空間傳輸Oracle
- Oracle傳輸表空間Oracle
- MySQL表空間傳輸MySql
- 10g新特性之-跨平臺表空間傳輸
- mysql之 表空間傳輸MySql
- 傳輸表空間操作-OracleOracle
- Oracle傳輸表空間（TTS）OracleTTS
- Oracle 傳輸表空間-RmanOracle
- 總結-表空間傳輸
- 跨平臺表空間遷移(傳輸表空間)
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移
- 基於可傳輸表空間的表空間遷移
- Oracle傳輸表空間學習Oracle
- Oracle 傳輸表空間-EXPDP/IMPDPOracle
- Oracle 傳輸表空間-EXP/IMPOracle
- 傳輸表空間自包含理解
- Oracle表空間傳輸詳解Oracle
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移[轉]
- Oracle 10g的可傳輸表空間操作（轉並驗證）Oracle 10g
- 10G新特性筆記之跨平臺傳輸表空間筆記
- oracle 傳輸表空間一例Oracle
- Oracle可傳輸表空間測試Oracle
- 5.7 mysql的可傳輸表空間MySql
- 表空間傳輸讀書筆記筆記
- Oracle9i傳輸表空間到10g測試過程Oracle
- RMAN跨小版本跨平臺與位元組序傳輸表空間
- 【XTTS】Oracle傳輸表空間xtts增量方式TTSOracle
- 傳輸表空間（從Linux到Windows）LinuxWindows
- oracle可傳輸表空間TTS小結OracleTTS
- oracle表空間傳輸的限制條件Oracle
- 實戰RMAN備份傳輸表空間