Oracle 12c 多租户 CDB 与 PDB 级别 expdb 与 impdb（表、用户、全库）

我不是女神ヾ 2022-01-09 07:19 325阅读 0赞

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 的服务名即可。

转载于:https://www.cnblogs.com/andy6/p/6832771.html