1 、使用前，创建逻辑目录directory
[oracle@was1 ~]$ mkdir -p /home/oracle/dumpdir/dumppdb1/
2 、 切换到pdb1 进数据库将其创建成逻辑目录：
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> alter session set container = pdb1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> create directory dumppdb1 as '/home/oracle/dumpdir/dumppdb1/';
Directory created.
3 、通过查询 dba_directories 可以看到新建 directory 的信息：
SQL> select * from dba_directories where DIRECTORY_NAME = 'DUMPPDB1';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ---------------------------------------- ---------------------------------------- -------------
SYS DUMPPDB1 /home/oracle/dumpdir/dumppdb1/ 3
4 、 单独创建一个dba权限的数据泵用户
SQL> create user jerry identified by oracle;
User created.
SQL> grant dba to dump_pdb1_user;
Grant succeeded.
SQL> grant read,write on directory DUMPPDB1 to dump_pdb1_user;
Grant succeeded.
可插拔数据库，在一个容器 cdb 中以多租户的形式同时存在多个数据库 pdb 。在为 pdb 做数据泵导入导出时和传统的数据库
有少许不同：需要为 pdb 添加 tansnames
5、将PDB1添加tnsnames，确保tnsping OK
[oracle@was1 admin]$ vi tnsnames.ora
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
pdb1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb1)
)
)
[oracle@was1 admin]$ tnsping pdb1
TNS Ping Utility for Linux: Version 21.0.0.0.0 - Production on 04-JAN-2023 16:55:52
Copyright (c) 1997, 2021, Oracle. All rights reserved.
Used parameter files:
/ora21c/app/homes/OraDB21Home1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.210.10)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb1)))
OK (0 msec)
6、准备环境，创建表空间、用户以及表：
SQL>create tablespace test datafile '/data/21c/oradata/ORA21C/F0564D828AC16531E0530AD2A8C085F9/datafile/test01.dbf' size 20M;
SQL> create user jerry identified by oracle default tablespace test;
User created.
SQL> grant dba to jerry;
Grant succeeded.
SQL> create user scott identified by oracle
User created.
SQL> grant dba to scott;
Grant succeeded.
在 jerryy 用户下创建两张表：
SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name='TEST';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
JERRY EMP TEST
JERRY DEPT TEST
7、tables模式导出
[oracle@was1 ~]$ expdp system/oracle@pdb1 directory=DUMPPDB1 dumpfile=jerry_table_emp.dump logfile=jerry_table_dump.log tables=jerry.emp
Export: Release 21.0.0.0.0 - Production on Wed Jan 4 17:11:02 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@pdb1 directory=DUMPPDB1 dumpfile=jerry_table_emp.dump logfile=jerry_table_dump.log tables=jerry.emp
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "JERRY"."EMP" 8.125 KB 14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/dumpdir/dumppdb1/jerry_table_emp.dump
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 4 17:11:38 2023 elapsed 0 00:00:29
8、tables模式导入
把 test 表空间 jerry 用户下的 emp 表导入到 users 表空间的 scott 用户下
先查看 USERS 表空间下的表：
SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name='USERS';
OWNER TABLE_NAME TABLESPACE_NAME
---------- ------------------------------ ------------------------------
TEST TAB1 USERS
执行导入：
[oracle@was1 dumppdb1]$ impdp scott/oracle@pdb1 directory=DUMPPDB1 dumpfile=jerry_table_emp.dump nologfile=y tables=jerry.emp remap_schema=jerry:scott remap_tablespace=test:users;
Import: Release 21.0.0.0.0 - Production on Wed Jan 4 18:00:11 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/********@pdb1 directory=DUMPPDB1 dumpfile=jerry_table_emp.dump nologfile=y tables=jerry.emp remap_schema=jerry:scott remap_tablespace=test:users
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP" 8.125 KB 14 rows
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jan 4 18:00:16 2023 elapsed 0 00:00:03
查看导入情况：
SQL> select owner,table_name,tablespace_name from dba_tables where tablespace_name='USERS';
OWNER TABLE_NAME TABLESPACE_NAME
---------- -------------------- --------------------
TEST TAB1 USERS
SCOTT EMP USERS
9、schemas 模式导入导出：
远端导出：
[oracle@was1 dumppdb1]$ expdp system/oracle@pdb1 directory=DUMPPDB1 dumpfile=jerry.dump nologfile=y schemas=jerry;
目标端导入：
impdp system/oracle@pdb1 directory=DUMPPDB1 dumpfile=jerry.dump nologfile=y schemas=jerry;
