Move a subset of public synonyms from one Oracle database to another

Today I had a task to move some public synonyms from a production database to one of the development instance. The keyword here is ‘some’.

The source database has thousands of objects and the destination database just a subset of several hundreds. Public synonyms has to be created for existing objects only, but not for all of them and just for the ones that exist in production. If I had DBLINK between databases that would be a matter of joining DBA_SYNONYMS from the source database to the DBA_OBJECTS of the destination. However, it wasn’t possible because of firewalls and many other reasons.

So, what I did is pretty straightforward and simple:

1. Create ALL synonyms in the development database from the production. The trick here is to eliminate all of the Oracle default users. This list depends on options you have installed in the database.

SELECT 'create public synonym '|| s.synonym_name ||' for '
|| s.table_owner || '.' || s.synonym_name || ';'
FROM dba_synonyms s
WHERE owner='PUBLIC'
AND table_owner NOT IN ('SYS','SYSTEM','SYSMAN','OUTLN','XDB','ANONYMOUS',
'ORACLE_OCM','FLOWS_FILES','DBSNMP','SI_INFORMTN_SCHEMA','APEX_030200','APPQOSSYS',
'OLAPSYS','CTXSYS','DMSYS','DSSYS','EXFSYS','ORDSYS','MDSYS','WMSYS','TSMSYS')
ORDER BY table_owner;

2.  Run the result from the previous step on the development database. It will create all synonyms, but as the result you will be getting “ORA-01775: looping chain of synonyms” for non-existent objects.

3. Now we have to drop some synonyms for non-existent objects on the development side. To get this list we need to execute something like this:

SELECT 'drop public synonym ' || synonym_name || ';'
FROM dba_synonyms s
WHERE s.owner='PUBLIC' AND NOT EXISTS 
(select 1 from dba_objects o 
 where s.table_owner=o.owner and s.table_name=o.object_name);

4. Run the result from the step 3 in the development. It will drop some of the synonyms created previously.

Now we have subset of synonyms from the production database for the objects that are exists in the development.

If you have a better way of doing it without a database link please let me know in the comments.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>