Oracle 12c pdb使用expdp/impdp导入导出
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 12c pdb使用expdp/impdp导入导出，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含7764字，纯文字阅读大概需要12分钟。
内容图文
1、查看当前的SID，查看pdb并切换到容器数据库,这里的pluggable数据库是pdborcl
[oracle@test admin]$ echo $ORACLE_SID [oracle@test admin]orcl
登录cdb，查看pdb,
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- PDB$SEED READ ONLY NO PDBORCL MOUNTED SQL> alter pluggable database all open; Pluggable database altered. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- PDB$SEED READ ONLY NO PDBORCL READ WRITE NO
切换到pdborcl
SQL> alter session set container=pdborcl; Session altered. SQL>
2、查看示例用户scott，以后的schema级别导入导出就使用该用户的数据。
SQL> select owner, table_name from dba_tables where owner='SCOTT'; OWNER TABLE_NAME ------------------------------ ---------------------------------------- SCOTT SALGRADE SCOTT BONUS SCOTT EMP SCOTT DEPT
3、单独创建一个dba权限的数据泵用户
SQL> grant dba to dp identified by dp; Grant succeeded.
4、创建一个数据泵目录dp_dir，路径为oracle家目录
SQL> create or replace directory dp_dir as '/home/oracle'; Directory created. SQL> exit
5、授予dp用户在数据泵路径有读写权限
（如果是dba权限的这一步可以省略，为了试验的完整性这里保留）
SQL> grant read,write on directory dp_dir to dp; Grant succeeded.
6、设置tnsnames.ora，增加pdborocl。SERVICE_NAME为pdb的实例名，这里为pdborcl
[oracle@xqzt admin]$ pwd /data/app/oracle/product/12.1.0/dbhome_1/network/admin [oracle@xqzt admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /data/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) PDBORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =pdborcl) ) )
7、测试tnsnames.ora的有效性，如果返回OK (0 msec)表示配置成功
[oracle@xqzt admin]$ tnsping pdborcl TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-DEC-2015 09:10:34 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /data/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xqzt)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =pdborcl))) OK (0 msec)
8、数据泵导出
用户名密码为dp/dp，并且通过tnsnames指向pdborcl
数据泵目录为：dp_dir, OS路径是/home/oracle
导出文件为：/home/oracle/scott_pdborcl.dmp
导出日志为：/home/oracle/scott_pdborcl.log
导出模式为scheme，也可以理解为用户：scott
[oracle@xqzt ~]$ expdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott Export: Release 12.1.0.2.0 - Production on Thu Dec 10 09:32:05 2015Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "DP"."SYS_EXPORT_SCHEMA_01": dp/********@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER . . exported "SCOTT"."DEPT" 6.023 KB 4 rows . . exported "SCOTT"."EMP" 8.773 KB 14 rows . . exported "SCOTT"."SALGRADE" 6.023 KB 10 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "DP"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for DP.SYS_EXPORT_SCHEMA_01 is: /home/oracle/scott_pdborcl.dmp Job "DP"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Dec 10 09:32:29 2015 elapsed 0 00:00:21 [oracle@xqzt ~]$
10、查看导出文件
[oracle@xqzt ~]$ ls -l scott_pdborcl.dmp scott_pdborcl.log -rw-r----- 1 oracle oinstall 356352 12月 10 09:32 scott_pdborcl.dmp -rw-r--r-- 1 oracle oinstall 1960 12月 10 09:32 scott_pdborcl.log
11、为了测试导出文件是否能够正常导入，我们先删除pdborcl的scott用户
SQL> select count(*) from scott.DEPT; COUNT(*) ---------- SQL> drop user scott cascade ; User dropped. SQL>
此时访问该用户的表已经不存在了
SQL> select count(*) from scott.DEPT; select count(*) from scott.DEPT *ERROR at line 1: ORA-00942: table or view does not exist
12、 导入scott用户
[oracle@xqzt ~]$ impdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott Import: Release 12.1.0.2.0 - Production on Thu Dec 10 09:39:02 2015Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "DP"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded Starting "DP"."SYS_IMPORT_SCHEMA_01": dp/********@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."DEPT" 6.023 KB 4 rows . . imported "SCOTT"."EMP" 8.773 KB 14 rows . . imported "SCOTT"."SALGRADE" 6.023 KB 10 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/STATISTICS/MARKER Job "DP"."SYS_IMPORT_SCHEMA_01" successfully completed at Thu Dec 10 09:39:06 2015 elapsed 0 00:00:04 [oracle@xqzt ~]$
13、 测试导入结果
SQL> select count(*) from scott.DEPT; COUNT(*) ---------- 4
导入成功！
Oracle 12c pdb使用expdp/impdp导入导出
标签：12c impdp expdp
本文系统来源：http://blog.51cto.com/liuqun/2052654
内容总结
以上是互联网集市为您收集整理的Oracle 12c pdb使用expdp/impdp导入导出全部内容，希望文章能够帮你解决Oracle 12c pdb使用expdp/impdp导入导出所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】