目录
Full Transportable Export/Import: Example
只传输除了system，sysaux，temp，undo以外的用户表空间，這種方式傳輸的是用戶自定義的表空間，這種方式適用於资料量大的数据库，比前面我所讲的方式快。
这种方式导出导入不需要在目标数据库创建和源数据库一样的表空间，因为可以直接把表空间的元数据导入进去。
数据文件+元数据
查看pdb1
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
SQL> alter session set container=pdb1;
Session altered.
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
SYSTEM /u01/app/oracle/oradata/CDB/pdb1/system01.dbf
SYSAUX /u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
UNDOTBS1 /u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
USERS /u01/app/oracle/oradata/CDB/pdb1/users01.dbf
SQL> alter tablespace users read only;
Tablespace altered.
SQL> select username from all_users;------NICE用户是pdb1中的用户
USERNAME
--------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
SYSBACKUP
SYSDG
SYSKM
SYSRAC
OUTLN
DBSFWUSER
XS$NULL
GSMADMIN_INTERNAL
USERNAME
--------------------------------------------------------------------------------
GSMUSER
DIP
REMOTE_SCHEDULER_AGENT
DBSNMP
ORACLE_OCM
SYS$UMF
APPQOSSYS
GSMCATUSER
GGSYS
XDB
ANONYMOUS
USERNAME
--------------------------------------------------------------------------------
WMSYS
OJVMSYS
CTXSYS
ORDSYS
ORDDATA
ORDPLUGINS
SI_INFORMTN_SCHEMA
MDSYS
OLAPSYS
MDDATA
DVSYS
USERNAME
--------------------------------------------------------------------------------
LBACSYS
DVF
PDBADMIN
HR
NICE
38 rows selected.
创建pdb2
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/system01.dbf
/u01/app/oracle/oradata/CDB/sysaux01.dbf
/u01/app/oracle/oradata/CDB/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/system01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/CDB/users01.dbf
/u01/app/oracle/oradata/CDB/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf
14 rows selected.
SQL> alter session set container=pdb2;
Session altered.
SQL> startup;
Pluggable Database opened.
SQL> SELECT file_name, tablespace_name FROM dba_data_files;
SQL> col file_name for a50
SQL> set linesize 200
SQL> /
FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf SYSTEM
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf UNDOTBS1
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------------------ --------------------------------------------------
TEMP /u01/app/oracle/oradata/CDB/pdb2/temp012024-01-10_
16-24-28-422-PM.dbf
从pdb1 中导出元数据
[oracle@19c scripts]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@19c scripts]$ expdp system/oracle@pdb1 \
> full=y \
> dumpfile=11g.dmp \
> directory=dump_dir \
> transportable=always \#表示导出的数据可以在不同的数据库之间进行传输和导入
> logfile=dump.log
#以上这个命令执行完后最后回提示我们需要传输两个数据文件到目标数据库，因为我们是在pdb之间传输，所以我们可以直接永cp命令，本来如果是在两台主机上传输的话还需要传输11.dmp文件，但是我们这里就不需要了
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/scripts/11g.dmp
******************************************************************************
Datafiles required for transportable tablespace USERS:
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sat Jan 13 19:00:45 2024 elapsed 0 00:06:32
[oracle@19c scripts]$ ll -ls
total 218528
3920 -rw-r-----. 1 oracle oinstall 4014080 Jan 13 19:00 11g.dmp
3920 -rw-r-----. 1 oracle oinstall 4014080 Jan 10 17:52 cai.dmp
12 -rw-r--r--. 1 oracle oinstall 11240 Jan 13 19:00 dump.log
4 -rw-r-----. 1 oracle oinstall 434 Jan 10 18:08 export.log
872 -rw-r-----. 1 oracle oinstall 892620 Jan 11 23:13 import.log
209800 -rw-r-----. 1 oracle oinstall 214835200 Jan 11 17:07 jie.dmp
[oracle@19c pdb1]$ cp /u01/app/oracle/oradata/CDB/pdb1/users01.dbf /u01/app/oracle/oradata/CDB/pdb2/CDB
[oracle@19c pdb1]$ cd /u01/app/oracle/oradata/CDB/pdb2/CDB
[oracle@19c CDB]$ ll -ls
total 5128
0 drwxr-x---. 3 oracle oinstall 22 Jan 13 19:19 0EB5BD04C2C06932E06302F4A8C08F19
5128 -rw-r-----. 1 oracle oinstall 5251072 Jan 13 19:35 users01.dbf在pdb2中导入元数据（dmp文件）
执行这个命令就可以导入，在导入前，如果目标数据库和被导出的数据库的表空间名字相同，可以提前rename表空间的明在再执行导入。一下可以看出这样的导入方式就很高效率。
[oracle@19c CDB]$ impdp system/oracle@pdb2 \
> full=y \
> dumpfile=11g.dmp \
> directory=dump_dir \
> transport_datafiles='/u01/app/oracle/oradata/CDB/pdb2/CDB/users01.dbf' \
> logfile=imp.log
本文含有隐藏内容，请 开通VIP 后查看