host command complete
/*
The following command may be used to import the tablespaces.
Substitute values for and .
impdp directory= dumpfile= 'dmpfile.dmp' transport_datafiles= /u02/transport/tspitr01.dbf, /u02/transport/test01.dbf
*/
--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/u02/transport/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR 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 := 'dmpfile.dmp';
dump_file.directory_object := 'STREAMS$DIROBJ$DPDIR';
-- 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;
DROP DIRECTORY STREAMS$DIROBJ$DPDIR;
--------------------------------------------------------------
-- 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_kayd.f deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_system_bkb7lb6k_.dbf deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_undotbs1_bkb7lbfr_.dbf deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_sysaux_bkb7lb6y_.dbf deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/datafile/o1_mf_temp_bkb7q2ql_.tmp deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/onlinelog/o1_mf_1_bkb7pnmt_.log deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/onlinelog/o1_mf_2_bkb7pqk4_.log deleted
auxiliary instance file /u02/transport/TSPITR_TEST_KAYD/onlinelog/o1_mf_3_bkb7ptbb_.log deleted
5.将步骤4生成的传输表空间的数据文件tspitr01.dbf,test01.dbf和Data Pump导出文件dmpfile拷贝到目标主机上的/u02目录中
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/tspitr01.dbf /u02
oracle@192.168.56.2's password:
tspitr01.dbf 100% 100MB 7.1MB/s 00:14
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/test01.dbf /u02
oracle@192.168.56.2's password:
test01.dbf 100% 10MB 10.0MB/s 00:00
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/transport/dmpfile.dmp /u02
oracle@192.168.56.2's password:
dmpfile.dmp 100% 96KB 96.0KB/s 00:00
[oracle@jingyong1 u02]$ ls -lrt
total 112876
-rw-r----- 1 oracle oinstall 104865792 Mar 27 17:54 tspitr01.dbf
-rw-r----- 1 oracle oinstall 10493952 Mar 27 18:22 test01.dbf
-rw-r----- 1 oracle oinstall 98304 Mar 27 18:22 dmpfile.dmp
6.在目录主机上创建相关用户及Data Pump目录并将表空间附加到目标数据库中
SQL> create user tspitr identified by "tspitr";
User created.
SQL> grant dba,connect,resource to tspitr;
Grant succeeded.
SQL> create user test identified by "test";
User created.
SQL> grant dba,connect,resource to test;
Grant succeeded.
SQL> create directory mytest as '/u02';
Directory created.
SQL> grant read,write on directory mytest to public;
Grant succeeded.