--------------------------------------------------------------
-- Start of sample PL/SQL script for importing the tablespaces
--------------------------------------------------------------
-- creating directory objects
CREATE DIRECTORY STREAMS$DIROBJ$1 AS '/s01/dd/';
CREATE DIRECTORY STREAMS$DIROBJ$DPDIR AS '/s01/dd';
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 := 'tt01.dbf';
tbs_files( 1).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
database closed
database dismounted
Oracle instance shut down
Automatic instance removed
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_temp_9kj3l8ph_.tmp deleted
auxiliary instance file /s01/dd/HJJ/onlinelog/o1_mf_3_9kj3l3lr_.log deleted
auxiliary instance file /s01/dd/HJJ/onlinelog/o1_mf_2_9kj3l20t_.log deleted
auxiliary instance file /s01/dd/HJJ/onlinelog/o1_mf_1_9kj3l0b0_.log deleted
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_sysaux_9kj3g706_.dbf deleted
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_undotbs1_9kj3g77n_.dbf deleted
auxiliary instance file /s01/dd/HJJ/datafile/o1_mf_system_9kj3g6mv_.dbf deleted
auxiliary instance file /s01/dd/HJJ/controlfile/o1_mf_9kj3fpmt_.ctl deleted
传输完成后会自动删除临时目录/s01/dd下的文件。
查看目录/s01/dd
[root@myrac1 dd]# ll
total 102508
-rw-r----- 1 oracle asmadmin 90112 Mar 5 22:00 dmpfile.dmp
drwxr-x--- 5 oracle asmadmin 4096 Mar 5 21:25 HJJ
-rw-r--r-- 1 oracle oinstall 2022 Mar 5 22:00 impscrpt.sql
-rw-r----- 1 oracle asmadmin 104865792 Mar 5 21:57 tt01.dbf
impscrpt.sql --元数据信息，目标数据库必须存在相应的对象属主，数据文件路径不一致还要修改脚本。
dmpfile.dmp --也是元数据信息，如果使用dump文件导入元数据信息则就没有上面的限制了。
tt01.dbf --数据文件，可以发现生成的数据文件和原来的数据文件一模一样。
HJJ是/s01/dd下的一个临时目录。
8.目标数据库中导入元数据集
a.使用asm存储体系，使用dump导入
convert datafile '/opt/tts/td/tts.288.755520393' format '+datagroup';
如果是使用的asm则需要把os上面的文件放到asm里面，上面这条命令可以在os和asm之间转换数据文件。
impdp system/oracle@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='+DATAGROUP/ORCL/DATAFILE/TTS.290.755528197';
b.使用文件系统，使用dump导入。
impdp system/oracle@orcl directory=dp dumpfile=dmpfile.dmp nologfile=y transport_datafiles='/opt/oradata/tts.288.755520393';
c.@/opt/tts/td/impsctpt.sql
9.复制源数据库/s01/dd目录和数据文件到目标数据库D:\dd
D:\dd>ls
dmpfile.alg dmpfile.dmp impscrpt.sql tt01.dbf
10.检查源数据库和目标数据库的数据块大小是否一致
源数据库：
SQL> select block_size from dba_tablespaces where tablespace_name = 'TT1';
BLOCK_SIZE
----------
8192
目标数据库：
SQL> show parameter db_block_size