• 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 create restore points for PDB and perform flashback at PDB level
  2. How To Run ggsci In “silent” Mode
  3. Steps to relink Oracle Forms 12c in Linux/UNIX
  4. What is SQL Server Operating System ( SQLOS)
  5. How to get the DDL for indexes using dbms_metadata
  6. Oracle 20c New Feature: PDB Point-in-Time Recovery or Flashback to Any Time
  7. ORA-00031: session marked for kill
  8. Oracle Database – How to Recover from a Lost or Deleted Datafile with Different Scenarios
  9. Beginners Guide to Monitoring Oracle Database Restore/Recovery Progress
  10. How to Find OCR Master Node

You May Also Like

Primary Sidebar

Recent Posts

  • raw: command not found
  • raw Command Examples in Linux
  • rankmirrors Command Examples in Linux
  • radeontop: command not found

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright