Category : Databases and related files
Archive   : UTIL91.ZIP
Filename : BDROPPUB.SQL

rem BDROPPUB.SQL - This SQL*Plus script builds a SQL*Plus
rem script that may be run to drop all
rem PUBLIC synonyms in specified account.
rem You must have DBA privilege to run this
rem script and have the DBA synonyms created in
rem your account.
rem
rem Syntax:
rem $ SQLPLUS-S USERNAME/PASSWORD @BDROPPUB TABLE_OWNER
rem
rem Set up the SQL*Plus controls
rem
set echo off
set termout off
set feedback off
set verify off
set pause off
set linesize 80
set pagesize 0
rem
define ownername = "&1"
rem
spool droppub.sql
rem
rem SQL statements
rem

select 'drop PUBLIC synonym '||synonym_name||';'
from dba_synonyms
where table_owner like upper('&ownername')
and owner = 'PUBLIC'
order by synonym_name;

spool off
exit