Abhay, hasn't the original poster explicitely specified he doesn't have DBA privileges in his very first message? So how would he drop schema?
It has to be done object by object, no other way around, not with the description of the problem he provided. Ther's no magic "DROP ALL MY OBJECTS" command as he was hoping. Of course, using SQL to generate scripts to drop all objects is trivial and will speed up the dropping extensively. Jim has provided him the sample for the tables - the remaining stuff for all other type of objects should be easy.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
A few changes to Pando's script make it generic and without the DBA dependency:
set termout on
set head off feedback off verify off termout off
Rtrim('drop '||object_type||' '
||object_name||decode(object_type,'TABLE',' cascade constraints')
|| ';' )
order by object_name
set head on feedback on verify on
set echo on
set echo off
set pagesize 14 termout on
But it does not detract from the fact that the original post requested how to remove all tables and not all objects. Unless the poster is clear in thier requirements than confusion like this is bound to occur.
Oracle Certified Professional "Build your reputation by helping other people build theirs."
"Sarcasm may be the lowest form of wit but its still funny"