-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS? '/u02/transport/';
/* PL/SQL Script to import the exported tablespaces */
DECLARE
? -- the datafiles
? tbs_files? ? dbms_streams_tablespace_adm.file_set;
? cvt_files? ? dbms_streams_tablespace_adm.file_set;
? -- the dumpfile to import
? dump_file? ? dbms_streams_tablespace_adm.file;
? dp_job_name? VARCHAR2(30) := NULL;
? -- names of tablespaces that were imported
? ts_names? ? ? dbms_streams_tablespace_adm.tablespace_set;
BEGIN
? -- dump file name and location
? dump_file.file_name :=? 'test.dmp';
? dump_file.directory_object := 'test_dump';
? -- forming list of datafiles for import
? tbs_files( 1).file_name :=? 'tspitr01.dbf';
? tbs_files( 1).directory_object :=? 'STREAMS$DIROBJ$1';
? tbs_files( 2).file_name :=? 'test01.dbf';
? tbs_files( 2).directory_object :=? 'STREAMS$DIROBJ$1';
? -- import tablespaces
? dbms_streams_tablespace_adm.attach_tablespaces(
? ? datapump_job_name? ? ? => dp_job_name,
? ? dump_file? ? ? ? ? ? ? => dump_file,
? ? tablespace_files? ? ? => tbs_files,
? ? converted_files? ? ? ? => cvt_files,
? ? tablespace_names? ? ? => ts_names);
? -- output names of imported tablespaces
? IF ts_names IS NOT NULL AND ts_names.first IS NOT NULL THEN
? ? FOR i IN ts_names.first .. ts_names.last LOOP
? ? ? dbms_output.put_line('imported tablespace '|| ts_names(i));
? ? END LOOP;
? END IF;
END;
/
-- dropping directory objects
DROP DIRECTORY STREAMS$DIROBJ$1;
--------------------------------------------------------------
-- End of sample PL/SQL script
--------------------------------------------------------------
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
auxiliary instance file /u02/transport/cntrl_tspitr_TEST_meiB.f deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/datafile/o1_mf_system_bkf2s69f_.dbf deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/datafile/o1_mf_undotbs1_bkf2s6b2_.dbf deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/datafile/o1_mf_sysaux_bkf2s69q_.dbf deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/datafile/o1_mf_temp_bkf2ydmv_.tmp deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/onlinelog/o1_mf_1_bkf2xxxc_.log deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/onlinelog/o1_mf_2_bkf2xzww_.log deleted
auxiliary instance file /u02/transport/TSPITR_TEST_MEIB/onlinelog/o1_mf_3_bkf2y2qn_.log deleted
[oracle@oracle11g transport]$ ls -lrt
total 112784
drwxr-x--- 4 oracle oinstall? ? ? 4096 Mar 28 19:06 TSPITR_TEST_MEIB
-rw-r----- 1 oracle oinstall 104865792 Mar 28 19:09 tspitr01.dbf
-rw-r----- 1 oracle oinstall? 10493952 Mar 28 19:09 test01.dbf
-rw-r--r-- 1 oracle oinstall? ? ? 2065 Mar 28 19:10 importtest.sql
[oracle@oracle11g dump_test]$ ls -lrt
total 104
-rw-r--r-- 1 oracle oinstall? 1242 Mar 28 19:10 testexport.log
-rw-r----- 1 oracle oinstall 98304 Mar 28 19:10 test.dmp
从上面的结果可以看到data pump导出文件为test.dmp,导出日志文件为testexport.log,示例导入脚本为importtest.sql，与指定的名称一样。
5.将步骤4生成的传输表空间的数据文件tspitr01.dbf,test01.dbf和Data Pump导出文件dmpfile拷贝到目标主机上的/u02目录中
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transp