• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle Database : script to create a “CREATE SYNONYM Script”

by admin

The following is a script that once run will generate another script that will include all the create synonym statements for all those in the database, both private and public.

Pre-requisites

1. This script must be run by a user with the DBA role.

The script

Running this script will in turn create a script to build all the synonyms in the database. This created script, create_synonyms.sql, can be run by any user with the DBA role or with the ‘CREATE ANY SYNONYM’ and ‘CREATE PUBLIC SYNONYM’ system privileges.

NOTE: This script does not capture synonyms created on tables owned by the ‘SYS’ user.
set verify off
set feedback off
set termout off
set echo off
set pagesize 0

set termout on
select 'Creating synonym build script...' from dual;
set termout off

spool create_synonyms.sql

select 'CREATE '|| decode(owner,'PUBLIC','PUBLIC ',null) ||
       'SYNONYM ' || decode(owner,'PUBLIC',null, owner || '.') ||
        lower(synonym_name) || ' FOR ' || lower(table_owner) ||
        '.' || lower(table_name) ||
        decode(db_link,null,null,'@'||db_link) || ';'
  from sys.dba_synonyms
  where table_owner != 'SYS'
  order by owner
/
spool off

exit

Sample output:

CREATE PUBLIC SYNONYM temp$lob FOR system.def$_temp$lob;                        
CREATE PUBLIC SYNONYM product_profile FOR system.product_privs;                 
CREATE PUBLIC SYNONYM product_user_profile FOR system.product_privs;            
CREATE PUBLIC SYNONYM user_profile FOR system.user_privs;                       
CREATE PUBLIC SYNONYM table_dependency FOR system.table_dependency;             
CREATE PUBLIC SYNONYM slt FOR scott.linktest@V815.WORLD;                        
CREATE SYNONYM SYS.def$_aqcall FOR system.def$_aqcall;                          
CREATE SYNONYM SYS.def$_calldest FOR system.def$_calldest;                      
CREATE SYNONYM SYS.def$_schedule FOR system.def$_schedule;                      
CREATE SYNONYM SYS.def$_error FOR system.def$_error;                            
CREATE SYNONYM SYS.def$_defaultdest FOR system.def$_defaultdest;                
CREATE SYNONYM SYS.def$_lob FOR system.def$_lob;

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to find the Character set of Oracle Database
  2. How To Cancel A SQL Query In Oracle Database 18c
  3. Understanding SQL Joins – Inner, Left, Right & Full Joins
  4. How to Identify Different File types and space used in Flash Recovery Area
  5. Find Oracle RAC OCR & voting disk location
  6. Table Vs. Materialized View
  7. Handling Exceptions in PL/SQL
  8. PL/SQL Nested Blocks
  9. Oracle RMAN Pluggable Database Point in Time Recovery
  10. CentOS / RHEL : Installing and Configuring ASMLib

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright