oracle pdb导出,Oracle 12c pdb使用expdp/impdp导入导出

1，需要为pdb添加tansnames2，导入导出时需要在userid参数内指定其tansnames的值，比如 [emailprotected]数据泵导出1、查看当前的SID，查看pdb并切换到容器数据库,这里的pluggable数据库是pdborcl[[emailprotected]admin]$echo$ORACLE_SID[[emailprotected]admin]orcl登...

GreedyAbyss

2006人浏览 · 2021-04-11 21:04:30

GreedyAbyss · 2021-04-11 21:04:30 发布

1，需要为pdb添加tansnames

2，导入导出时需要在userid参数内指定其tansnames的值，比如 [email protected]

数据泵导出

1、查看当前的SID，查看pdb并切换到容器数据库,这里的pluggable数据库是pdborcl[[email protected] admin]$ echo $ORACLE_SID

[[email protected] 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

切换到pdborclSQL> 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[[email protected] admin]$ pwd

/data/app/oracle/product/12.1.0/dbhome_1/network/admin

[[email protected] 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)表示配置成功[[email protected] admin]$ tnsping pdborcl

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 10-DEC-2015 09:10:34

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[[email protected] ~]$ expdp [email protected] directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott

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": [email protected] 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

[[email protected] ~]$

10、查看导出文件[[email protected] ~]$ 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用户[[email protected] ~]$ impdp [email protected] directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott

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": [email protected] 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

[[email protected] ~]$

13、测试导入结果SQL> select count(*) from scott.DEPT;

COUNT(*)

----------

导入成功！

原文：http://blog.51cto.com/liuqun/2052654

魔乐社区

魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。

更多推荐

全家桶集齐！Qwen3.5四款小模型上线魔乐社区，附昇腾全套实践教程

魔乐社区

Pont - 搭建前后端之桥：高效、灵活的接口管理工具

Pont 是一款强大的数据服务层解决方案，它能够帮助开发者快速搭建前后端之间的桥梁，实现接口的高效管理和代码自动生成。无论是新手还是有经验的开发者，都能通过 Pont 轻松处理接口文档、生成类型安全的 API 代码，从而显著提升开发效率。[![Pont 工具标志](https://raw.gitcode.com/gh_mirrors/po/pont/raw/3f1b7d4bbba3fd2dda

魔乐社区

如何快速上手 hvac：HashiCorp Vault Python 客户端零基础入门指南

**hvac** 是 HashiCorp Vault 的 Python 3.X 客户端库，专为开发者提供简单高效的 Vault 交互方式。无论你是需要管理密钥、配置身份验证，还是实现安全的秘密数据存储，hvac 都能帮助你轻松搞定 Vault 的各项操作。本文将带你零基础快速入门，从安装到基础操作，让你在几分钟内即可上手使用这个强大的工具。[![hvac 客户端 Logo](https://r