expdp和 impdp 使用 include 导出和导入指定对象

需要导入的 DB LINK 的名字是 TEST
impdp system/oracle   directory=expdir dumpfile=exp_%U.dump logfile=imp.log  include=DB_LINK:\"=\'TEST\'\"

============================================================================
--导出和导入公共同义词
============================================================================
--创建公共同义词
SQL>
create PUBLIC SYNONYM public_t1 for lixia.t1;

--查看同意词在数据泵中的对象类型
SQL> select * from DATABASE_EXPORT_OBJECTS  where OBJECT_PATH like '%SYNONYM%';

OBJECT_PATH                                        COMMENTS                                           N
-------------------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM      Public and private synonyms
PUBLIC_SYNONYM/SYNONYM                             Public and private synonyms
SCHEMA/PUBLIC_SYNONYM/SYNONYM                      Public and private synonyms
SYNONYM                                            Public and private synonyms                        Y

--使用 include 参数导出指定的公共同义词
expdp system/oracle   directory=EXPDIR dumpfile=exp_synonym%U.dump logfile=exp_synonym.log  full=y  include=SYNONYM:\"=\'PUBLIC_T1\'\"

--删除公共同义词
SQL>
drop public synonym public_t1;

--导入公共同义词 PUBLIC_T1
impdp system/oracle   directory=EXPDIR dumpfile=exp_synonym%U.dump logfile=imp_synonym.log  full=y  include=SYNONYM:\"=\'PUBLIC_T1\'\"

--验证公共同意次 public_t1 导入成功
SQL> select * from dba_synonyms where synonym_name='PUBLIC_T1';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
PUBLIC                         PUBLIC_T1                      LIXIA                          T1

--问题：

问题：指定导出公共同义词报 ORA-39168 和 ORA-31655 错误

[oracle@oradb dump]$ expdp system/oracle   directory=EXPDIR dumpfile=exp_synonym%U.dump logfile=exp_synonym.log   include=SYNONYM:\"=\'PUBLIC_T1\'\"

Export: Release 11.2.0.4.0 - Production on Sun Sep 4 17:06:31 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_02":  system/******** directory=EXPDIR dumpfile=exp_synonym%U.dump logfile=exp_synonym.log include=SYNONYM:"='PUBLIC_T1'"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
ORA-39168: Object path SYNONYM was not found.
ORA-31655: no data or metadata objects selected for job
Job "SYSTEM"."SYS_EXPORT_SCHEMA_02" completed with 2 error(s) at Sun Sep 4 17:06:37 2016 elapsed 0 00:00:05

原因：没指定使用 FULL 方式导出，EXPDP只在 SYSTEM scheme 中寻找公共同义词 PUBLIC_T1

处理：加上 full =y
expdp system/oracle   directory=EXPDIR dumpfile=exp_synonym%U.dump logfile=exp_synonym.log  full=y  include=SYNONYM:\"=\'PUBLIC_T1\'\"




============================================================================================
--导入和导出 PROFILE
============================================================================================

select * from DATABASE_EXPORT_OBJECTS  where OBJECT_PATH like '%PROFILE%';

OBJECT_PATH                                        COMMENTS                                           N
-------------------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/PROFILE                            Profiles
PROFILE                                            Profiles                                           Y


select profile from dba_profiles group by profile;

PROFILE
------------------------------
MONITORING_PROFILE
TEST
TEST2
DEFAULT

--导出指定的 PROFILE
expdp system/oracle   directory=EXPDIR dumpfile=exp_profile%U.dump logfile=exp_profile.log  full=y  include=PROFILE:\"=\'TEST\'\"

--删除指定的 PROFILE
SQL>
drop profile test;

--导入指定的 PROFILE
impdp system/oracle   directory=EXPDIR dumpfile=exp_profile%U.dump logfile=imp_profile.log  full=y  include=PROFILE:\"=\'TEST\'\"

--检查 PROFILE导入成功
select profile from dba_profiles group by profile;

PROFILE
------------------------------
MONITORING_PROFILE
TEST
TEST2
DEFAULT



============================================================================================
--导入和导出 角色
============================================================================================
--查看角色在数据泵中的对象类型为 ROLE

select * from DATABASE_EXPORT_OBJECTS  where OBJECT_PATH like '%ROLE%';

OBJECT_PATH                                        COMMENTS                                           N
-------------------------------------------------- -------------------------------------------------- -
DATABASE_EXPORT/DVPS_POLICY/DVPS_COMMAND_RULE_GROU Database Vault Role definitions
P/DVPS_ROLE

DATABASE_EXPORT/ROLE                               Roles
DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE                Default roles granted to users in the database
DATABASE_EXPORT/SCHEMA/ROLE_GRANT                  Role grants to users in the database
DEFAULT_ROLE                                       Default roles granted to users in the database
DVPS_COMMAND_RULE_GROUP/DVPS_ROLE                  Database Vault Role definitions
DVPS_POLICY/DVPS_COMMAND_RULE_GROUP/DVPS_ROLE      Database Vault Role definitions
DVPS_ROLE                                          Database Vault Role definitions                    Y
ROLE                                               Roles                                              Y

OBJECT_PATH                                        COMMENTS                                           N
-------------------------------------------------- -------------------------------------------------- -
ROLE_GRANT                                         Role grants to users in the database
SCHEMA/DEFAULT_ROLE                                Default roles granted to users in the database
SCHEMA/ROLE_GRANT                                  Role grants to users in the database

12 rows selected.


--创建测试用的角色
SQL>
CREATE ROLE role_test IDENTIFIED BY 123456;
GRANT CREATE SESSION TO role_test;

SQL> select role from dba_roles where role='ROLE_TEST';

ROLE
------------------------------
ROLE_TEST

SQL> select * from dba_sys_privs where grantee='ROLE_TEST';   

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
ROLE_TEST                      CREATE SESSION                           NO



--导出指定的 ROLE
expdp system/oracle   directory=EXPDIR dumpfile=exp_role%U.dump logfile=exp_role.log  full=y  include=ROLE:\"=\'ROLE_TEST\'\"

expdp system/oracle   directory=EXPDIR dumpfile=exp_role_u%U.dump logfile=exp_role_u.log   SCHEMAS=ROLE_TEST


--删除指定的 ROLE
SQL>
drop role role_test;

--导入指定的 ROLE
impdp system/oracle   directory=EXPDIR dumpfile=exp_role%U.dump logfile=imp_role.log  full=y  include=ROLE:\"=\'ROLE_TEST\'\"




--检查 ROLE 导入了，但角色的权限没导入
SQL> select role from dba_roles where role='ROLE_TEST';

ROLE
------------------------------
ROLE_TEST

SQL> select * from dba_sys_privs where grantee='ROLE_TEST';

no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-2124103/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-2124103/