12c pdb使用数据泵导入导出

最新推荐文章于 2025-02-03 13:46:58 发布

他乡觅知音

最新推荐文章于 2025-02-03 13:46:58 发布

阅读量2.6k

点赞数 5

分类专栏： oracle 文章标签： 12c pdb使用数据泵导入导出

本文链接：https://blog.youkuaiyun.com/louisjh/article/details/80901526

版权

oracle 专栏收录该内容

23 篇文章

订阅专栏

本文介绍如何在Oracle 12c环境中通过数据泵进行数据的导出与导入操作。首先展示了如何查看当前的SID及PDB，并切换到容器数据库。接着指导创建数据泵目录并授予用户权限，最后详细说明了数据泵导出与导入的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.查看当前的SID，查看pdb并切换到容器数据库,这里的pluggable数据库是orcl

[oracle@orcl ~]$ echo $ORACLE_SID
zzorcl
[oracle@orcl ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 3 16:40:56 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
SQL>

2.切换到插件库

SQL> alter session set container = orcl;

Session altered.

3.可以自己创建数据泵目录，也可以用oracle默认目录，这里以目录dp_dir，路径为oracle家目录

SQL> create or replace directory dp_dir as  '/home/oracle';

Directory created.

SQL> exit

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

SQL> grant read,write on directory dp_dir to dp;
Grant succeeded.

5.设置tnsnames.ora，增加pdborcl。SERVICE_NAME为orcl,下面是我在TNSNAMES.ora 里加的

pdbORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zzorcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

  ZZORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = zzorcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = zzorcl)
    )
  )

LISTENER_ZSQ =
  (ADDRESS = (PROTOCOL = TCP)(HOST =zzorcl)(PORT = 1521))

6.数据泵导出、导入(scott为例子)

expdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl.log schemas=scott


impdp dp/dp@pdborcl directory=dp_dir dumpfile=scott_pdborcl.dmp logfile=scott_pdborcl_imp.log schemas=scott