需要导入的 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
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/