Oracle 12c 多租戶 CDB 與 PDB 級別 expdb 與 impdb（表、使用者、全庫）

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/oracle@10.100.25.16: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/andy@10.100.25.16: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/andy@10.100.25.16: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/andy@10.100.25.16: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/andy@10.100.25.16: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/andy@10.100.25.16: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/andy@10.100.25.16: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 的服務名即可。