oracle12c下简单使用expdp&impdp

在12c下一切变得不是很熟悉了，毕竟相对之前还是有的

创建授权用户等操作

SQL> show user;
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> create user c##dp_u1 identified by oracle;

User created.

SQL> grant dba to c##dp_u1; -- 这里只是cdb中的权限

Grant succeeded.

SQL> grant dba to c##dp_u1 container=all; -- 所有pdb都有权限

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1PDB                        MOUNTED
         4 PDB2                           READ WRITE NO

SQL> alter session set container=pdb2;

Session altered.

SQL> conn c##dp_u1/oracle@pdb2
Connected.
SQL> show user;
USER is "C##DP_U1"
SQL> create directory dp_dir1 as '/home/oracle/bak';
create directory dp_dir1 as '/home/oracle/bak'
*
ERROR at line 1:
ORA-65254: invalid path specified for the directory

SQL> create or replace directory dp_dir1 as 'bak';

Directory created.

SQL> select directory_path from dba_directories where directory_name='DP_DIR1';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/pdb1/bak

SQL> ! mkdir -p /u01/app/oracle/oradata/cdb1/pdb1/bak

注意这里创建的用户跟之前不太一样：

1，12c中，账号分为两种，一种是公用账号，一种是本地账号；在cdb创建用户是全局的，相对应的是pdb中创建的账户
2，在pdb中如果有个用户名，在cdb中不能创建相同的；反过来也一样
3，在cdb中创建的全局账号比如以c##开头
4，默认授权只在相应的地方有效如果全局有效需要在cdb中附权时添加container=all

导出操作：

[oracle@centos7 db_1]$ expdp c##dp_u1/oracle@pdb2 directory=dp_dir1 dumpfile=t100.dmp tables=t100

Export: Release 12.2.0.1.0 - Production on Sun Nov 26 21:24:03 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "C##DP_U1"."SYS_EXPORT_TABLE_01":  c##dp_u1/********@pdb2 directory=dp_dir1 dumpfile=t100.dmp tables=t100 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "C##DP_U1"."T100"                           5.070 KB       1 rows
Master table "C##DP_U1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for C##DP_U1.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/oradata/cdb1/pdb1/bak/t100.dmp
Job "C##DP_U1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Nov 26 21:25:41 2017 elapsed 0 00:01:34

删除表再进行导入操作

[oracle@centos7 db_1]$ impdp c##dp_u1/oracle@pdb2 directory=dp_dir1 dumpfile=t100.dmp tables=t100

Import: Release 12.2.0.1.0 - Production on Sun Nov 26 21:28:30 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "C##DP_U1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "C##DP_U1"."SYS_IMPORT_TABLE_01":  c##dp_u1/********@pdb2 directory=dp_dir1 dumpfile=t100.dmp tables=t100 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "C##DP_U1"."T100"                           5.070 KB       1 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "C##DP_U1"."SYS_IMPORT_TABLE_01" successfully completed at Sun Nov 26 21:29:58 2017 elapsed 0 00:01:20