CODEWITHSUNDEEP
oracle-databaseplsqlplsqldevelopersynonym
Serdar Alkan
Serdar Alkan

Reputation: 161

oracle set public synonym all table or other object?

I have productionDB and testDB.ProductionDB have synonym for tables.And now i want to create synonym for all tables for testDB.How i can create synonym all of them. I can get list synonym this query

 select *
 from all_synonyms s
 join all_objects o
 on s.table_owner = o.owner
 and s.table_name = o.object_name
 where s.table_owner = 'XYZ'

Upvotes: 0

Views: 1425

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Try the following query:

SELECT CAST(dbms_metadata.get_ddl(object_type => 'SYNONYM',
                                  name        => a.synonym_name,
                                  SCHEMA      => a.owner) AS VARCHAR2(4000))
  FROM ALL_SYNONYMS a;

Share and enjoy.

Upvotes: 2

jim mcnamara
jim mcnamara

Reputation: 16389

This is a starter script - it writes sql as output. Run it, READ the output first. Uncomment the last line when you think it is correct - it has to be run with privilege.

set pages 0
set feed off
set linesize 180
set trimspool on
spool syn.sql
select 'CREATE PUBLIC SYNONYM ' || SYNONYM_NAME | ' FOR ' ||
       TABLE_OWNER || '.' || TABLE_NAME || ';'
       from ALL_SYNONYMS
       where DB_LINK is NULL;
select 'CREATE PUBLIC SYNONYM ' || SYNONYM_NAME | ' FOR ' ||
       TABLE_OWNER || '.' || TABLE_NAME || '@' || DB_LINK || ';'
       from ALL_SYNONYMS
       where DB_LINK is NOT NULL;

spool off
--   uncomment after it has been tested 
[email protected]

If testDB was created using a PROD export then why are the synonyms all missing? Something is very wrong here. OR I am missing something.

Upvotes: 1

Related Questions