set echo off
----------------------------------------------------------------------------------------
--
-- Name: c_pubsynonyms_nonsys.sql
--
-- Desc: Synonyms - Creates public synonyms for all objects in all schemas (except sys, system) that don't already have a public synonym.
--
-- Author: Michael Pearce
--
-- Usage: @c_pubsynonyms_nonsys
--
-- Revision History:
--
-- DATE WHO WHAT
-- --------- ---------- ------------------
--
--
----------------------------------------------------------------------------------------
set termout off
set verify off
set doc off
set feedback off
set recsep off
set pagesize 0
--
define lsize=200
set linesize &&lsize
--
define out_file=c_pubsynonyms.ddl
spool &&out_file
--
SELECT 'create public synonym '||rtrim(OBJECT_NAME)||' for '||OWNER|| '.' ||rtrim(OBJECT_NAME)||';'
FROM all_objects
WHERE object_type IN ('TABLE','PACKAGE','PROCEDURE','FUNCTION','VIEW','SEQUENCE')
AND OWNER not in ('SYSTEM', 'SYS')
AND not exists (SELECT null
FROM dba_synonyms
WHERE owner = 'PUBLIC'
AND dba_synonyms.synonym_name = all_objects.object_name
AND dba_synonyms.table_owner = all_objects.owner);
spool off
set doc off
set feedback on
set pagesize 30
set termout on
set echo on
start &&out_file
-- exit
|