Use datapump to export the import synonym (exportandimportsynonymusingdatapump)

You can use multiple methods to back up synonyms. For example, you can directly generate a synonym creation script or use dbms_metadata.get_ddl to extract the definition of synonyms.

You can use multiple methods to back up synonyms. For example, you can directly generate a synonym creation script or use dbms_metadata.get_ddl to extract the definition of synonyms.

You can use multiple methods to back up synonyms. For example, you can use a script to generate a creation script for synonyms or use dbms_metadata.get_ddl to extract definition scripts for synonyms. However, when using the traditional exp or datapump expdp for schema-level data migration, public synonyms cannot be exported. However, we can still use the Export and Import method. The difference is that we use the FULL = Y method to export the synonym separately, and then import it to the target database. The following is a description of this, and a script for manually creating synonyms is also provided at the end.

1. Environment

-- Source database
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

-- Target database
-- All the data in the schema goex_admin of the target database comes from the source database, but no synonym exists after the data is imported using 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

-- There are only two synonyms in the following query. These two synonyms are created manually when the database is created, instead of imported using datapump.
SQL> select count (*) from dba_synonyms where table_owner = 'goex _ admin ';

COUNT (*)
----------
2

2. Export public synonyms from the source database

Oracle @ BKDB01p: ~> Env | grep SID
ORACLE_SID = XM6320
# Use the following method to export public synonyms. For exported objects, we can query the data dictionary 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,200 5, 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