使用datapump 匯出匯入同義詞(export and import synonym using datapump)
對於同義詞的備份我們有多種方式來實現,如直接透過指令碼生成同義詞的建立指令碼,或者使用dbms_metadata.get_ddl來提取同義詞的定義指令碼。然而在使用傳統的exp或是datapump expdp實現schema級別資料遷移時,不能匯出公共同義詞。儘管如此,我們依舊可以使用匯出匯入的方式來實現。所不同的是,我們使用FULL=Y的方式來單獨匯出同義詞,然後再將其匯入的目標資料庫。下文是對此進行的描述,末尾也給出了手動建立同義詞的指令碼。
1、環境
- --源資料庫
- SQL> select * from v$version where rownum<2;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- SQL> show parameter db_nam
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_name string XM6320
- SQL> select count(*) from dba_synonyms where table_owner='GOEX_ADMIN';
- COUNT(*)
- ----------
- 1042
- --目標資料庫
- --目標資料庫schema goex_admin的所有資料均來源於源資料庫,但是使用datapump匯入後,無同義詞
- SQL> select * from v$version where rownum<2;
- BANNER
- ----------------------------------------------------------------
- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- SQL> show parameter db_name
- NAME TYPE VALUE
- ------------------------------------ ----------- ------------------------------
- db_name string KM3625
- --下面的查詢中僅有兩個同義詞,這兩個同義是在建立DB的時候手動建立的,非使用datapump匯入產生的
- SQL> select count(*) from dba_synonyms where table_owner='GOEX_ADMIN';
- COUNT(*)
- ----------
- 2
2、從源資料庫匯出公共同義詞
- oracle@BKDB01p:~> env | grep SID
- ORACLE_SID=XM6320
- #使用下面的方式匯出公共同義詞,對於可匯出的物件我們可以查詢資料字典 DATABASE_EXPORT_OBJECTS
- oracle@BKDB01p:~> expdp goex_admin/xxx directory=db_dump_dir dumpfile=syns.dmp logfile=exp_syns.log full=y \
- > include=PUBLIC_SYNONYM/SYNONYM:\"IN \(SELECT synonym_name FROM dba_synonyms WHERE table_owner=\'GOEX_ADMIN\'\)\"
- Export: Release 10.2.0.3.0 - 64bit Production on Wednesday, 19 June, 2013 16:11:19
- Copyright (c) 2003, 2005, Oracle. All rights reserved.
- Connected to: Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
- Starting "GOEX_ADMIN"."SYS_EXPORT_FULL_01": goex_admin/******** directory=db_dump_dir dumpfile=syns.dmp logfile=exp_syns.log
- full=y include=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='GOEX_ADMIN')"
- Estimate in progress using BLOCKS method...
- Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
- Total estimation using BLOCKS method: 0 KB
- Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
- Master table "GOEX_ADMIN"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
- ******************************************************************************
- Dump file set for GOEX_ADMIN.SYS_EXPORT_FULL_01 is:
- /u02/database/XM6320/BNR/dump/syns.dmp
- Job "GOEX_ADMIN"."SYS_EXPORT_FULL_01" successfully completed at 16:11:23
3、匯入公共同義詞到目標資料庫
- oracle@BKDB01p:~> cp /u02/database/XM6320/BNR/dump/syns.dmp /u02/database/KM3625/BNR/dump/
- oracle@BKDB01p:~> export ORACLE_SID=KM3625
- oracle@BKDB01p:~> impdp goex_admin/xxx directory=db_dump_dir dumpfile=syns.dmp 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、手動建立同義詞的指令碼
- ACCEPT input_owner PROMPT 'Enter the owner of table:'
- SET HEADING OFF VERIFY OFF FEEDBACK OFF TERMOUT OFF;
- SPOOL $LOG/create_synonym.sql
- --Author: Robinson
- --Blog : http://blog.csdn.net/robinson_0612
- 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;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1098047/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Run Out Of Space On UNDO Tablespace Using DataPump Import/ExportImportExport
- DataPump Export/Import Of LOBs Are Not Executed in ParallelExportImportParallel
- Speedup Datapump ImportImport
- 【metalink】Export/Import DataPump Parameter TRACE (文件 ID 286496.1)ExportImport
- 匯出oracle公有同義詞Oracle
- react-native 之匯入(import)、匯出(export)深刻解析ReactImportExport
- 完全可傳輸的匯出/匯入(full transportable export/import)ExportImport
- Export/Import匯入匯出時的字符集問題ExportImport
- DB2資料的匯入(Import) 匯出(Export)(Load)DB2ImportExport
- 匯出Oracle中的同義詞Oracle
- 使用DATAPUMP升級DB與Oracle Text索引無法成功匯入Oracle索引
- 詳解同義詞(synonym)
- Oracle中的同義詞SYNONYMOracle
- Oracle10g Export/Import DataPump Does Not Work with Tapes or UNIX Named Pipes [ID 276521.1]OracleExportImport
- 【DATAPUMP】使用DataPump遷移Oracle資料庫Oracle資料庫
- Import Datapump Job fails with ORA-39125ImportAI
- 【匯出匯入】匯出匯入 大物件物件
- Oracle之處理synonym同義詞無效物件Oracle物件
- C#可擴充套件程式設計之MEF學習筆記(二):MEF的匯出(Export)和匯入(Import)C#套件程式設計筆記ExportImport
- ORA-31626 ORA-31650 While Using DataPump Export in RAC Environment [ID 454639.1]WhileExport
- DataPump Export (EXPDP) Fails With Error LPX-216 Invalid CharacterExportAIError
- Minimum Requirements to Use Export DataPump and IMP(System Privileges)_351598.1UIREMExport
- 【匯入匯出】sqlldr 匯入案例SQL
- 【匯出匯入】% 在匯入匯出中的應用。
- 【原創】匯出所有物件(表、索引、檢視、同義詞)的建立指令碼物件索引指令碼
- 【匯入匯出】將資料匯入到其他使用者
- Activity 流程模型匯入匯出-activity流程模型匯入匯出模型
- exp/imp匯出匯入工具的使用
- 如何使用 JavaScript 匯入和匯出 ExcelJavaScriptExcel
- about datapump parallelParallel
- Mysql匯入&匯出MySql
- Mysql匯入匯出MySql
- doris匯入匯出
- go 語言模組匯入importGoImport
- java匯出Excel定義匯出模板JavaExcel
- Subject: Bug 3827736 - LPX-225 during datapump import with statisticsImport
- Oracle使用資料泵匯出匯入表Oracle
- Oracle exp/imp匯出匯入工具的使用Oracle