-- #############################################################################################
--
-- %Purpose: Show Synonyms for all Schmea Owners
--
-- #############################################################################################
--
set feed off;
set pagesize 10000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
spool show_synonyms.lst
break on table_owner skip 1
column datum new_value datum noprint
column owner format A12 heading 'Synonym|Owner'
column table_owner format A12 heading 'Table|Owner'
column table_name format A26 heading 'Table|Name'
column synonym_name format A26 heading 'Synonym|Name'

select  to_char(sysdate, 'MM/DD/YY') datum,
  substr(table_owner,1,12) table_owner,
  substr(table_name,1,26) table_name,
  substr(synonym_name,1,26) synonym_name,
  substr(owner,1,12) owner
from dba_synonyms
where table_owner not in ('SYS','SYSTEM','DBSNMP');
order by 1,2,3,4;
spool off;