Transport_Tablespace-EXPDP/IMPDP

將 192.168.3.199 資料庫下， chenjc 使用者下的 t1 表，匯入到 192.168.3.198 資料庫下， chenjc 使用者下；

一 檢視作業系統版本，資料庫版本

192.168.3.199

[oracle@ogg1 ~]$ cat /etc/issue

Oracle Linux Server release 6.3

SQL> select * from v$version where rownum<=2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

192.168.3.198

[oracle@ogg2 orcl]$ cat /etc/issue

Oracle Linux Server release 6.3

SQL> select * from v$version where rownum<=2;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

二 建立測試表空間，測試使用者，測試表

192.168.3.199

SQL> create tablespace chenjc datafile '/u01/app/oracle/oradata/orcl/chenjc01.dbf' size 30m autoextend on;

Tablespace created.

SQL> create user chenjc identified by chenjc default tablespace chenjc;

User created.

SQL> grant connect,resource,dba to chenjc;

Grant succeeded.

SQL> conn chenjc/chenjc

Connected.

SQL> create table t1 as select level id,sysdate as t_date from dual connect by level<=100000;

Table created.

三 檢查準備遷移的表空間是否自包含

SQL> conn /as sysdba

Connected.

SQL> execute dbms_tts.transport_set_check(ts_list=>'CHENJC',incl_constraints=>TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

/* 無返回記錄，說明符合傳輸表空間條件*/

四 設定準備傳輸的表空間為只讀

SQL> alter tablespace chenjc read only;

Tablespace altered.

五 透過 expdp 工具匯出說要傳輸表空間的原資料

[oracle@ogg1 ~]$ mkdir dir_dp

SQL> create directory dir_dp as '/home/oracle/dir_dp';

Directory created.

SQL> grant read,write on directory dir_dp to chenjc;

Grant succeeded.

[oracle@ogg1 ~]$ expdp system/oracle dumpfile=chenjc_01.dmp directory=dir_dp transport_tablespaces=chenjc logfile=chenjc_01.log

Export: Release 11.2.0.3.0 - Production on Mon Aug 3 11:11:26 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=chenjc_01.dmp directory=dir_dp transport_tablespaces=chenjc logfile=chenjc_01.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

/home/oracle/dir_dp/chenjc_01.dmp

******************************************************************************

Datafiles required for transportable tablespace CHENJC:

/u01/app/oracle/oradata/orcl/chenjc01.dbf

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:11:51

/*

模擬平臺轉換（同一平臺傳輸不需要這步）

SQL> col platform_name for a35

SQL> select * from v$transportable_platform order by platform_id;

RMAN>convert tablespace "TESTSPACE" to platform 'Microsoft Windows IA (32-bit)' format 'd:\TESTSPACE01.DBF' --這個是轉換的目標地址

*/

六 將資料庫檔案和匯出的表空間原檔案複製到 192.168.3.198 伺服器

[oracle@ogg1 ~]$ cd dir_dp/

[oracle@ogg1 dir_dp]$ scp chenjc_01.dmp 192.168.3.198:/home/oracle/

[oracle@ogg1 dir_dp]$ scp /u01/app/oracle/oradata/orcl/chenjc01.dbf 192.168.3.198:/home/oracle/

192.168.3.198

[oracle@ogg2 ~]$ mv chenjc* /u01/app/oracle/oradata/orcl/

[oracle@ogg2 ~]$ cd /u01/app/oracle/oradata/orcl/

[oracle@ogg2 orcl]$ ll -rth

......

-rw-r----- 1 oracle oinstall 88K Aug 3 11:13 chenjc_01.dmp

-rw-r----- 1 oracle oinstall 31M Aug 3 11:13 chenjc01.dbf

......

七 目標資料庫建立使用者，指定表空間 ( 目標資料庫不能有和將要傳輸表空間同名的表空間 )

SQL> create user chenjc identified by chenjc default tablespace users;

User created.

SQL> grant connect,resource,dba to chenjc;

Grant succeeded.

八 透過 impdp 工具匯入表空間

[oracle@ogg2 orcl]$ mkdir ddir_dp

[oracle@ogg2 orcl]$ mv chenjc_01.dmp ddir_dp/

SQL> create directory ddir_dp as '/u01/app/oracle/oradata/orcl/ddir_dp';

Directory created.

SQL> grant read,write on directory ddir_dp to chenjc;

Grant succeeded.

[oracle@ogg2 ddir_dp]$ impdp system/oracle dumpfile=chenjc_01.dmp directory=ddir_dp transport_datafiles='/u01/app/oracle/oradata/orcl/chenjc01.dbf' logfile=chenjc_01.log

Import: Release 11.2.0.3.0 - Production on Mon Aug 3 11:26:53 2015

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=chenjc_01.dmp directory=ddir_dp transport_datafiles=/u01/app/oracle/oradata/orcl/chenjc01.dbf logfile=chenjc_01.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:27:02

九 修改使用者預設表空間

SQL> alter user chenjc default tablespace chenjc;

User altered.

十檢視

SQL> select name from v$dbfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/system.dbf

/u01/app/oracle/oradata/orcl/sysaux.dbf

/u01/app/oracle/oradata/orcl/undotbs01.dbf

/u01/app/oracle/oradata/orcl/user01.dbf

/u01/app/oracle/oradata/orcl/ggm01.dbf

/u01/app/oracle/oradata/orcl/chenjc01.dbf

6 rows selected.

SQL> conn chenjc/chenjc

SQL> select id,to_char(t_date,'yyyy-mm-dd hh24:mi:ss') from t1 where rownum<=3;

ID TO_CHAR(T_DATE,'YYY

---------- -------------------

1 2015-08-03 09:27:01

2 2015-08-03 09:27:01

3 2015-08-03 09:27:01

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！

Oracle 傳輸表空間-EXPDP/IMPDP

Oracle 傳輸表空間-EXPDP/IMPDP

相關文章