Oracle 19c 使用数据泵如何导入导出 PDB 用户

说起数据泵作为 oracle dba 而言，绝对不会陌生，但能够玩转数据泵的就比较少了，如果面试时问你使用数据泵的时候你都遇到了什么问题，该怎么回答？又有多少答案是你所不知道的呢？我想这也算是一个开放题，仁者见仁智者见智，每个人的回答都不一样。好了，进入今天的正题数据泵，还是遇到一个业务需求，导出某一库下的用户到另一容器数据库下，而你不知道这个普通用户密码，则需要 dba 用户来导入导出。

业务要求：将 19c 非容器数据库数据库（edw）下的用户 JIEKE_CSS 导入到另一个容器数据库(tsedw)的 PDB (EDW_T8)下。

那么，对于导出而言，没有业务账号密码则可以使用 DBA 账号轻而易举的导出来，但导入呢？直接使用 DBA 账号只能连接到 CDB，无法连接到 PDB 。下面我们来看看具体的过程。

1.查看导出目录

<code class="javascript">select * from dba_directories where directory_name ='DUMPDIR';set linesize 9999col OWNER for a10col DIRECTORY_NAME for a30col DIRECTORY_PATH for a60select * from dba_directories;--如果目录过小或者不存在,则类似如下新建一个create directory expdp_dir as '/u01/app/backup/expdp_dir';grant read,write on directory expdp_dir to public;</code>

2、导出用户

本次使用 nfs 挂载远程文件系统，对于 NFS 导入导出以及 rman 备份时不可用的坑也有不少，前面有好几篇文章已经分享过了，感兴趣的可以翻翻看。

<code class="javascript">nohup expdp \'/ as sysdba\' directory=DUMPDIR LOGFILE=JIEKE_CSS.log dumpfile=JIEKE_CSS-2021-0722.dmp SCHEMAS=JIEKE_CSS COMPRESSION=ALL PARALLEL=4 &</code>

因用户数据量及对象比较小，如下是导出日志。

<code class="javascript">Export: Release 19.0.0.0.0 - Production on Thu Jul 22 16:15:17 2021Version 19.4.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionStarting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DUMPDIR LOGFILE=JIEKE_CSS.log dumpfile=JIEKE_CSS-2021-0722.dmp SCHEMAS=JIEKE_CSS COMPRESSION=ALLProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATAProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT. . exported "JIEKE_CSS"."CHATLOG" 1.008 MB 5183 rows. . exported "JIEKE_CSS"."CALLLOG" 1.087 MB 14861 rows. . exported "JIEKE_CSS"."CHANNELLOG" 456.6 KB 5557 rows. . exported "JIEKE_CSS"."CCSERVER_SCORE" 199.4 KB 3184 rows. . exported "JIEKE_CSS"."DNDLOG" 116.3 KB 4282 rows. . exported "JIEKE_CSS"."EASYIVR_AGENTSCORE" 136.7 KB 2194 rows. . exported "JIEKE_CSS"."LEARNQUESTION" 53.91 KB 642 rows. . exported "JIEKE_CSS"."DC_AGENT_EXTEND" 16.54 KB 122 rows. . exported "JIEKE_CSS"."X_FP_SESSION_TAG" 9.476 KB 362 rows. . exported "JIEKE_CSS"."CC_CALL_TAG" 9.015 KB 338 rows. . exported "JIEKE_CSS"."FPAGENT" 9.070 KB 123 rows. . exported "JIEKE_CSS"."DC_CHANNEL" 7.101 KB 9 rows. . exported "JIEKE_CSS"."CCSERVER_ACCEPTNUMBER" 5.640 KB 7 rows. . exported "JIEKE_CSS"."CC_AGENT_STATUS_HISTORY" 0 KB 0 rows. . exported "JIEKE_CSS"."CC_LIST_POOL" 0 KB 0 rows. . exported "JIEKE_CSS"."CC_LOGS" 0 KB 0 rows. . exported "JIEKE_CSS"."CRM_INFOS" 0 KB 0 rows. . exported "JIEKE_CSS"."HAKEYDB_BACKUPCACHE" 0 KB 0 rows. . exported "JIEKE_CSS"."HAKEYDB_CONNECTION" 0 KB 0 rows. . exported "JIEKE_CSS"."HAKEYDB_NOQUERYLOG" 0 KB 0 rows. . exported "JIEKE_CSS"."HAKEYDB_QUERYLOG" 0 KB 0 rows. . exported "JIEKE_CSS"."LOGIC_AGENT_MSGBOX" 0 KB 0 rows. . exported "JIEKE_CSS"."LOGIC_COMMON_TERM" 0 KB 0 rows. . exported "JIEKE_CSS"."LOGIC_WO_ORDER" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_CUSTOM_STATUS_TIME_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_ONLINE_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_ONLINE_TIME_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_ORDER_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_QS_DETAIL_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_QS_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_SATISFY_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_SESSION_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_TRANSFER_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_VISITOR_MANUAL_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_VISITOR_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_AGENT_WORKGROUP_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_BLACKLIST_INTERCEPT_LOG_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_CC_AGENT_TRAFFIC_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_CC_BILL_LOG" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_CC_BILL_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_CC_FLOW_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_CC_GROUP_TRAFFIC_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_CC_TAG_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_COMMON_TERM_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_FULL_TEXT_SEARCH" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_LMSG_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_ORDER_STATUS_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_ORDER_TIMEOUT_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_ORDER_TRANSFER_LOG" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_ORDER_TYPE_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_SEND_MESSAGE_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_SENSITIVE_WORD_INTERCEPT_SDR" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_SESSION_TAG" 0 KB 0 rows. . exported "JIEKE_CSS"."ST_STORAGE_FILES" 0 KB 0 rows. . exported "JIEKE_CSS"."UPDATE_TIME_ES" 0 KB 0 rows. . exported "JIEKE_CSS"."UPDATE_TIME_MONGO" 0 KB 0 rows. . exported "JIEKE_CSS"."WO_ORDERS" 0 KB 0 rowsMaster table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:/oradata/dumpdir/JIEKE_CSS-2021-0722.dmpJob "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jul 22 16:19:18 2021 elapsed 0 00:03:35</code>

3、目标端容器数据库导入

容器数据库导入也就是要先连接到容器数据库，那么 Oracle 19c 连接到 PDB 也是有三四种方法的，这个在前年讲 19c 体验安装时就说过了《Oracle 19c 之多租户 PDB 连接与访问（三）》，无非也就是配置环境变量或者 tns 或者临时切换这样的，那么导入导出也是，下面先配置或者查看 tnsnames.ora ，以便可以使用 tns 直接登录到 pdb。

<code class="javascript">EDW_T8 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.126)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = edw_t8)))tsedw =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.126)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tsedw)))EDW =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.126)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = edw)))</code>

注意：要求是将 非容器 EDW 下的用户导入到容器数据库 tsedw 下的 PDB(EDW_T8) 实例中，故需要配置 PDB 的 TNS 连接。那么则可以使用远程连接 @EDW_T8 来直接登录进 PDB，但是需要使用密码，例如：sqlplus sys/oracle@EDW_T8 as sysdba

AI封面生成器

专业的AI封面生成工具，支持小红书、公众号、小说、红包、视频封面等多种类型，一键生成高质量封面图片。

下载

这样也可以满足要求将数据导入进去，但有的时候你也不知道 SYS 密码，像我们以前的公司一样，都可以登录服务器但就是你不知道管理员密码，就属于比较变态了，密码掌握在其他人手里，这时候你也很被动了，没有密码还想导入数据，那么怎么办呢？不要着急这里有一种办法帮你实现。

4、临时生效环境变量 ORACLE_PDB_SID

当在 shell 中直接生效如下的环境变量时，则可以直接登录到 PDB，那么，直接导入也就是导入到 PDB 实例了。

<code class="javascript">export ORACLE_PDB_SID=edw_t8</code>

5、导入前表空间查询与建立

查看原有表空间位置，发现 PDB 表空间属于自动管理，无需手动管理。

<code class="javascript">SQL> select file_id,file_name,bytes/1024/1024 mb from dba_data_files where TABLESPACE_NAME ='DWS_DATA';FILE_ID FILE_NAME MB---------- ------------------------------------------------------------------------------------------ ----------14 /oradata/TSEDW/ABF6D9ABF9216F9BE0537ED9000CECF2/datafile/o1_mf_dws_data_hlhrwqm6_.dbf 24655.3125</code>

<code class="javascript">--创建要导入用户的表空间（使用 OMF 特性）SQL> create tablespace JIEKE_CSS datafile size 500m;Tablespace created.SQL> col FILE_NAME for a90SQL> select file_id,file_name,bytes/1024/1024 mb from dba_data_files where TABLESPACE_NAME ='JIEKE_CSS';FILE_ID FILE_NAME MB---------- ------------------------------------------------------------------------------------------ ----------23 /oradata/TSEDW/ABF6D9ABF9216F9BE0537ED9000CECF2/datafile/o1_mf_huaqi_cs_jhlch5mk_.dbf 500</code>

6、目标端登入 PDB 查看数据导入存储目录DIRECTORY

<code class="javascript">Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.4.0.0.0SYS@tsedw> show pdbs    CON_ID CON_NAME                       OPEN MODE  RESTRICTED---------- ------------------------------ ---------- ----------         3 EDW_T8                         READ WRITE NO         SQL> set linesize 9999col OWNER for a10SQL> SQL> col DIRECTORY_NAME for a30SQL> col DIRECTORY_PATH for a60SQL> select * from dba_directories;OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID---------- ------------------------------ ------------------------------------------------------------ -------------SYS DMP /home/oracle/dmp 3SYS DUMP_DIR /home/oracle/dump_dir 3SYS DUMPDIR /oradata/dumpdir 3SYS SDO_DIR_WORK 1</code>

7、导入用户到 PDB

<code class="javascript">export ORACLE_PDB_SID=edw_t8impdp '/ as sysdba ' directory=DUMPDIR LOGFILE=imp_JIEKE_CSS.log dumpfile=JIEKE_CSS-2021-0722.dmp SCHEMAS=JIEKE_CSS PARALLEL=4Import: Release 19.0.0.0.0 - Production on Thu Jul 22 16:57:42 2021Version 19.4.0.0.0Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.Password:Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionMaster table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloadedStarting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" directory=DUMPDIR LOGFILE=imp_JIEKE_CSS.log dumpfile=JIEKE_CSS-2021-0722.dmp SCHEMAS=JIEKE_CSS PARALLEL=4Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "JIEKE_CSS"."CHANNELLOG" 456.6 KB 5557 rows. . imported "JIEKE_CSS"."CCSERVER_SCORE" 199.4 KB 3184 rows. . imported "JIEKE_CSS"."DNDLOG" 116.3 KB 4282 rows. . imported "JIEKE_CSS"."CHATLOG" 1.008 MB 5183 rows. . imported "JIEKE_CSS"."EASYIVR_AGENTSCORE" 136.7 KB 2194 rows. . imported "JIEKE_CSS"."LEARNQUESTION" 53.91 KB 642 rows. . imported "JIEKE_CSS"."DC_AGENT_EXTEND" 16.54 KB 122 rows. . imported "JIEKE_CSS"."X_FP_SESSION_TAG" 9.476 KB 362 rows. . imported "JIEKE_CSS"."CC_CALL_TAG" 9.015 KB 338 rows. . imported "JIEKE_CSS"."CALLLOG" 1.087 MB 14861 rows. . imported "JIEKE_CSS"."FPAGENT" 9.070 KB 123 rows. . imported "JIEKE_CSS"."DC_CHANNEL" 7.101 KB 9 rows. . imported "JIEKE_CSS"."CCSERVER_ACCEPTNUMBER" 5.640 KB 7 rows. . imported "JIEKE_CSS"."CC_AGENT_STATUS_HISTORY" 0 KB 0 rows. . imported "JIEKE_CSS"."CC_LIST_POOL" 0 KB 0 rows. . imported "JIEKE_CSS"."CC_LOGS" 0 KB 0 rows. . imported "JIEKE_CSS"."CRM_INFOS" 0 KB 0 rows. . imported "JIEKE_CSS"."HAKEYDB_BACKUPCACHE" 0 KB 0 rows. . imported "JIEKE_CSS"."HAKEYDB_CONNECTION" 0 KB 0 rows. . imported "JIEKE_CSS"."HAKEYDB_NOQUERYLOG" 0 KB 0 rows. . imported "JIEKE_CSS"."HAKEYDB_QUERYLOG" 0 KB 0 rows. . imported "JIEKE_CSS"."LOGIC_AGENT_MSGBOX" 0 KB 0 rows. . imported "JIEKE_CSS"."LOGIC_COMMON_TERM" 0 KB 0 rows. . imported "JIEKE_CSS"."LOGIC_WO_ORDER" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_CUSTOM_STATUS_TIME_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_ONLINE_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_ONLINE_TIME_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_ORDER_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_QS_DETAIL_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_QS_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_SATISFY_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_SESSION_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_TRANSFER_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_VISITOR_MANUAL_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_VISITOR_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_AGENT_WORKGROUP_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_BLACKLIST_INTERCEPT_LOG_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_CC_AGENT_TRAFFIC_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_CC_BILL_LOG" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_CC_BILL_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_CC_FLOW_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_CC_GROUP_TRAFFIC_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_CC_TAG_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_COMMON_TERM_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_FULL_TEXT_SEARCH" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_LMSG_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_ORDER_STATUS_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_ORDER_TIMEOUT_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_ORDER_TRANSFER_LOG" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_ORDER_TYPE_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_SEND_MESSAGE_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_SENSITIVE_WORD_INTERCEPT_SDR" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_SESSION_TAG" 0 KB 0 rows. . imported "JIEKE_CSS"."ST_STORAGE_FILES" 0 KB 0 rows. . imported "JIEKE_CSS"."UPDATE_TIME_ES" 0 KB 0 rows. . imported "JIEKE_CSS"."UPDATE_TIME_MONGO" 0 KB 0 rows. . imported "JIEKE_CSS"."WO_ORDERS" 0 KB 0 rowsORA-39083: Object type OBJECT_GRANT failed to create with error:ORA-01917: user or role 'R_SELECT_JIEKE_CSS' does not existFailing sql is:GRANT SELECT ON "JIEKE_CSS"."ST_FULL_TEXT_SEARCH" TO "R_SELECT_JIEKE_CSS"Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/STATISTICS/MARKERJob "SYS"."SYS_IMPORT_SCHEMA_01" completed with 44 error(s) at Thu Jul 22 16:58:31 2021 elapsed 0 00:00:38</code>

8、收集用户统计信息

<code class="javascript">SQL> exec dbms_stats.gather_schema_stats('JIEKE_CSS')PL/SQL procedure successfully completed.</code>

9、19c 连接及简单切换

<code class="javascript">$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 22 17:16:12 2021Version 19.4.0.0.0Copyright (c) 1982, 2019, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.4.0.0.0SYS@tsedw> show pdbsSYS@tsedw>CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEED READ ONLY NO3 EDW_T8 READ WRITE NOSYS@tsedw> show con_nameCON_NAME------------------------------CDB$ROOTSYS@tsedw> alter session set container=EDW_T8;Session altered.Elapsed: 00:00:00.00SYS@tsedw> show pdbsCON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------3 EDW_T8 READ WRITE NOSYS@tsedw> show con_nameCON_NAME------------------------------EDW_T8</code>