Exporting just public synonyms with expdp – possible or not?

 

man-96587_640

The short answer is yes it is possible though it’s not obvious from the docs:

Lets do a quick demo – first we check how many public synonyms we should be extracting

 select count(*) as total from dba_synonyms where owner='PUBLIC'
 and table_owner not in ('CTXSYS','EXFSYS','APPQOSSYS','XDB','SYS')
     TOTAL
----------
      1408



So excluding schemas that expdp ignores (the internal ones basically – we have 1408 to extract)

Ok so how do we get them out – there is no ‘PUBLIC_SYNONYM’ option as an include – or is there?

Lets try this:

expdp / full=y include=PUBLIC_SYNONYM/SYNONYM

Export: Release 11.2.0.3.0 - Production on Fri Sep 6 09:11:48 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Starting "OPS$ORACLE"."SYS_EXPORT_FULL_01":  /******** full=y include=PUBLIC_SYNONYM/SYNONYM
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Master table "OPS$ORACLE"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OPS$ORACLE.SYS_EXPORT_FULL_01 is:
  /oracle/11.2.0.3.1.DB/rdbms/log/expdat.dmp
Job "OPS$ORACLE"."SYS_EXPORT_FULL_01" successfully completed at 09:11:59

Looks like it’s done something at least……

Now lets do a dummy sqlfile import and see what we have in the file:

impdp / sqlfile=pubs.sql

Import: Release 11.2.0.3.0 - Production on Fri Sep 6 09:12:10 2013

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option
Master table "OPS$ORACLE"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "OPS$ORACLE"."SYS_SQL_FILE_FULL_01":  /******** sqlfile=pubs.sql
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Job "OPS$ORACLE"."SYS_SQL_FILE_FULL_01" successfully completed at 09:12:12

Now lets look for how many times we see the command ‘PUBLIC SYNONYM’ in that sql file.

grep "PUBLIC SYNONYM" /oracle/11.2.0.3.1.DB/rdbms/log/pubs.sql |wc -l
1408

So an exact match 1408 – the file does not contain normal ‘SYNONYMS’ as well it is just PUBLIC ones which can be confirmed by the command below – so it is possible!

cat /oracle/11.2.0.3.1.DB/rdbms/log/pubs.sql |grep -v "PUBLIC SYNONYM"
-- CONNECT OPS$ORACLE
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM

Comments

Post a Comment