使用datapump导出导入同义词(export and import synonym using datapump)(二)
logfile=imp_syns.log full=y include=synonym
Import: Release 10.2.0.3.0 - 64bit Production on Wednesday, 19 June, 2013 16:15:52
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
Master table "GOEX_ADMIN"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "GOEX_ADMIN"."SYS_IMPORT_FULL_01": goex_admin/******** directory=db_dump_dir dumpfile=syns.dmp logfile=imp_syns.log full=y include=synonym
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"PUBLIC"."GO_GA_SYS_DATAPUMP_PARA_TBL" already exists
ORA-31684: Object type SYNONYM:"PUBLIC"."BO_SYS_DATAPUMP_PKG" already exists
Job "GOEX_ADMIN"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 16:16:12
#上面的导入过程可以看到，Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM实现了同义词的导入
#同时由于有两个同义词存在，也给出了提示
#验证导入的同义词
oracle@BKDB01p:~> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Jun 19 16:16:24 2013
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string KM3625
SQL> select count(*) from dba_synonyms where table_owner='GOEX_ADMIN';
COUNT(*)
----------
1042
4、手动创建同义词的脚本
[sql]
ACCEPT input_owner PROMPT 'Enter the owner of table:'
SET HEADING OFF VERIFY OFF FEEDBACK OFF TERMOUT OFF;
SPOOL $LOG/create_synonym.sql
SELECT 'create public synonym ' || synonym_name || ' for ' || table_owner || '.' || table_name || ';'
FROM dba_synonyms s
WHERE s.owner = 'PUBLIC' AND s.table_owner = UPPER ('&input_owner');
SET HEADING ON VERIFY ON TERMOUT ON FEEDBACK OFF;
SPOOL OFF;
--@$LOG/create_synonym.sql;