Oracle 数据库 12 c 多租户下，如何在容器数据库 (CDB) 和可插拔数据库 (PDB) 中使用 expdb 与 impdp （数据泵） 呢？ 我们一起探讨下
PDB 下进行表级，用户级别，全库(PDB)导出 与 CDB 下进行表级，用户级别，全库(CDB)导出。导入类比导出即可。
实验环境：
SQL> select * from v$version;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0
一、 PDB级别数据泵
1.PDB表导出
[oracle@12c01 ~]$ sqlplus sys/[email protected]:1521/pdb01 as sysdba
SQL> create table c##andy.pdb_expdb as select object_name,object_id from dba_objects order by object_id fetch first 1 percent rows only;
Table created.
SQL> create directory datapump as '/home/oracle/datadump/';
Directory created.
SQL> grant dba to c##andy;
Grant succeeded.
SQL> commit;
Commit complete.
[oracle@12c01 ~]$ mkdir -p /home/oracle/datadump/
[oracle@12c01 ~]$$ expdp c##andy/[email protected]:1521/pdb01 directory=datapump tables=pdb_expdb dumpfile=pdb_expdb.dump logfile=export.log
Starting "C##ANDY"."SYS_EXPORT_TABLE_01": c##andy/********@10.100.25.16:1521/pdb01 directory=datapump tables=pdb_expdb dumpfile=pdb_expdb.dump logfile=export.log
.......
Job "C##ANDY"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 10 04:27:47 2017 elapsed 0 00:00:37
2.PDB用户导出
[oracle@12c01 ~]$ expdp c##andy/[email protected]:1521/pdb01 directory=datapump schemas=c##andy dumpfile=pdb01schemal.dump logfile=export.log
Starting "C##ANDY"."SYS_EXPORT_SCHEMA_01": c##andy/********@10.100.25.16:1521/pdb01 directory=datapump schemas=c##andy dumpfile=pdb01schemal.dump logfile=export.log
....
Job "C##ANDY"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 10 04:46:47 2017 elapsed 0 00:00:53
3.PDB全库导出
[oracle@12c01 ~]$ expdp c##andy/[email protected]:1521/pdb01 directory=datapump full=y dumpfile=pdb01full.dump logfile=export.log
Starting "C##ANDY"."SYS_EXPORT_FULL_01": c##andy/********@10.100.25.16:1521/pdb01 directory=datapump full=y dumpfile=pdb01full.dump logfile=export.log
.....
Job "C##ANDY"."SYS_EXPORT_FULL_01" successfully completed at Wed May 10 04:31:53 2017 elapsed 0 00:02:32
二、 CDB级别数据泵
1.CDB表导出
[oracle@12c01 datadump]$ sqlplus / as sysdba
SQL>create table c##andy.cdb_expdb as select object_name,object_id from dba_objects order by object_id fetch first 1 percent rows only;
SQL> grant dba to c##andy;
Grant succeeded.
创建directory
SQL> create directory datapump as '/home/oracle/datadump/';
Directory created.
[oracle@12c01 ~]$expdp c##andy/[email protected]:1521/andycdb directory=datapump tables=cdb_expdb dumpfile=cdb_expdb.dump logfile=export.log
Starting "C##ANDY"."SYS_EXPORT_TABLE_01": c##andy/********@10.100.25.16:1521/andycdb directory=datapump tables=cdb_expdb dumpfile=cdb_expdb.dump logfile=export.log
....
Job "C##ANDY"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 10 04:54:39 2017 elapsed 0 00:00:25
2.CDB用户导出
[oracle@12c01 ~]$ expdp c##andy/[email protected]:1521/andycdb directory=datapump schemas=c##andy dumpfile=andycdbschemal.dump logfile=export.log
Starting "C##ANDY"."SYS_EXPORT_SCHEMA_01": c##andy/********@10.100.25.16:1521/andycdb directory=datapump schemas=c##andy dumpfile=andycdbschemal.dump logfile=export.log
....
Job "C##ANDY"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed May 10 04:59:55 2017 elapsed 0 00:01:10
3.导出整个CDB
[oracle@dg2 ~]$ expdp c##andy/[email protected]:1521/andycdb directory=datapump full=y dumpfile=andycdbfull.dump logfile=export.log
Starting "C##ANDY"."SYS_EXPORT_FULL_01": c##andy/********@10.100.25.16:1521/andycdb directory=datapump full=y dumpfile=andycdbfull.dump logfile=export.log
....
Job "C##ANDY"."SYS_EXPORT_FULL_01" successfully completed at Wed May 10 05:06:17 2017 elapsed 0 00:03:01
总结：
CDB 与 PDB 级别的导入或导出，只要在连接时 指定 CDB 或 PDB 的服务名即可。