oracle12c下简单使用expdp&impdp

来源：互联网发布：淘宝达人怎么介绍自己编辑：程序博客网时间：2024/06/06 01:50

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

创建授权用户等操作

SQL> show user;USER is "SYS"SQL> show con_nameCON_NAME------------------------------CDB$ROOTSQL> 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 NOSQL> alter session set container=pdb2;Session altered.SQL> conn c##dp_u1/oracle@pdb2Connected.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 directorySQL> 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/bakSQL> ! 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=t100Export: Release 12.2.0.1.0 - Production on Sun Nov 26 21:24:03 2017Copyright (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 ProductionStarting "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_DATAProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERProcessing object type TABLE_EXPORT/TABLE/TABLE. . exported "C##DP_U1"."T100"                           5.070 KB       1 rowsMaster 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.dmpJob "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=t100Import: Release 12.2.0.1.0 - Production on Sun Nov 26 21:28:30 2017Copyright (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 ProductionMaster table "C##DP_U1"."SYS_IMPORT_TABLE_01" successfully loaded/unloadedStarting "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/TABLEProcessing object type TABLE_EXPORT/TABLE/TABLE_DATA. . imported "C##DP_U1"."T100"                           5.070 KB       1 rowsProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERJob "C##DP_U1"."SYS_IMPORT_TABLE_01" successfully completed at Sun Nov 26 21:29:58 2017 elapsed 0 00:01:20

阅读全文

0 0