rem Name:    drop.sql
rem Purpose: Drop database objects
rem Usage:   @drop <%owner.table%> <%type%>
rem Subject: object
rem Attrib:  sql gen ddl
rem Notes:   USE WITH CAUTION!
rem SeeAlso: @dropc @dropi @droprefs @dropsegs @dropt @dropu

@setup1
set heading off

define ty="UPPER('&&2')"

spool drop.tmp
select
  'DROP '||o.object_type||' '||o.owner||'.'||o.object_name||
  decode(o.object_type,
    'TABLE', ' CASCADE CONSTRAINTS',
    'CLUSTER', ' INCLUDING TABLES CASCADE CONSTRAINTS'
  )||';'
from &&ora._objects o
where o.owner <> 'SYS'
and o.owner like &&o1
and o.object_name like &&n1
and o.object_type like &&ty
and o.object_type not in ('PACKAGE BODY')
order by o.owner, o.object_name
;
spool off

set feedback on
@doit
@drop.tmp

undef ty

@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips