注意事项：
大数据量需要长时间上传时，
1.ssh工具，要设置反空闲
2.windows设置不自动休眠
3.服务器操作目录下空间要足够用
4.expdp、impdp 只能操作服务器目录上的文件, dump文件要拷贝到对应的服务器上
5.目标库字符集，要可源库字符集一致，否则会出现字段不够用的情况

实验环境：

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添加tansnames

2，导入导出时需要在userid参数内指定其tansnames的值，比如 userid=user/pwd@tnsname

               如： system@pdb01 （system用户方式最简单，因为不需要新建dp账户授权等操作）
         或 sys/oracle@10.100.25.16:1521/pdb01 （这种方式实验时总是报tns不对的错，）

常用参数：
tables=         表级别
schemas=     用户级别
full=y           全库
CONTENT    指定要加载的数据 [ALL], DATA_ONLY 和 METADATA_ONLY。
TABLE_EXISTS_ACTION 导入对象已存在时执行的操作。 APPEND, REPLACE, [SKIP] 和 TRUNCATE。
NETWORK_LINK 源系统的远程数据库链接的名称。
在有两个oracle库情况下，A库上建立dblink，可以在A库上，导出B库的数据
expdp system/oracle directory=dump dumpfile=scott_test.dmp logfile=scott_log.dmp network_link='power1' schemas=scott
impdp system/oracle directory=impdp_dir network_link='power1' schemas=test_a remap_schema=test_a:test_b
注：system/oracle是客户端本地的数据库认证，network_link参数值为所创建的dblink，schemas=scott为远程服务端的schemas

1）使用新建普通账号（dp）导出导入：

创建一个dba权限的数据泵用户

grant dba,create session to dp identified by dp;

pdb库内创建一个数据泵目录dp_dir

alter session set container=pdb;

create or replace directory dp_dir as '/u01/dump';

授予dp用户在数据泵路径有读写权限

grant read,write on directory dp_dir to dp;

设置tnsnames.ora，增加pdb01。SERVICE_NAME为pdb的实例名，这里为pdb01

PDB01= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)
      (SERVICE_NAME =pdb01)
    )
  )

数据泵导出

expdp dp/dp@pdb01 directory=dp_dir dumpfile=scott_pdb01.dmp logfile=scott_pdb01.log schemas=scott

数据泵导入

drop user scott cascade ; impdp dp/dp@pdb01 directory=dp_dir dumpfile=scott_pdb01.dmp logfile=scott_pdb01_imp.log schemas=scott

导入时，如果此指定的用户（schemas=scott）已经存在，会提示用户已经存在，如果没指定覆盖原有的表，...，呵呵，还是最好在导入前删除用户

2）system用户导入导出

SQL> alter session set container=pdb01;
Session altered.

SQL> create or replace directory dp_dir as '/u01/dump';
Directory created.

设置tnsnames.ora，增加pdb01。SERVICE_NAME为pdb的实例名，这里为pdb01

PDB01= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521))
    (CONNECT_DATA = (SERVER = DEDICATED)
      (SERVICE_NAME =pdb01)
    )
  )

expdp system@pdb01 directory=dp_dir dumpfile=scott_pdb01.dmp logfile=scott_pdb01.log schemas=scott

impdp system@pdb01 directory=dp_dir dumpfile=scott.dmp logfile=imp_scott.log schemas=scott

3）sys用户导入导出：

sys/oracle@10.100.25.16:1521/pdb01

这种方式导入时，总是说tns不对，
UDI-12504: operation generated ORACLE error 12504
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

这样ok的，但必须带上密码，否则进不去，这有点挫！
sqlplus username/passwd@//host:port/pdb

附：
同时多个dump文件，分别导入到原有的schema中
impdp system@pdb01 directory=dp_dir dumpfile=tdscdb.dmp,tdywdsdb.dmp,kbdb.dmp,tdrptdb.dmp,tdywds_user.dmp,ykgame.dmp logfile=imp.log

--------------摘录-----------------------------

Oracle 数据库 12 c 多租户下:
1. PDB 下进行表级，用户级别，全库(PDB)导出
2. CDB 下进行表级，用户级别，全库(CDB)导出。

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

ITPub博客

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

最新文章