set echo off
--
--  drop_create_pubsyn.sql    
--
--  Synonyms - Drop and recreate all public synonyms for the current user/schema login
--
--  Usage: @drop_create_pubsyn.sql
--
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=drop_create_pubsyn.ddl
spool &&out_file
--
SELECT
   'drop public synonym ' || rtrim(OBJECT_NAME)||';',
   'create public synonym '||rtrim(OBJECT_NAME)||' FOR ' , user|| '.' ||rtrim(OBJECT_NAME)||';'
FROM user_objects
WHERE object_type IN ('TABLE','PACKAGE','PROCEDURE','FUNCTION','VIEW','SEQUENCE')
ORDER by 3
;
spool off
set doc off
set feedback on
set pagesize 30
set termout on
set echo on
start &&out_file
-- exit