Delete all table in the schema with a single command

[abhaysk]

User creation/grant/assign_profile script wont do?

[davey23uk]

so whats the difference between writing a script to drop all objects and writing a script to create users then do all the grants, roles and profiles?

A script to create a user is specific each time, a script to drop objects can be used over and over again

[jmodic]

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.

[abhaysk]

Originally posted by davey23uk
A script to create a user is specific each time, a script to drop objects can be used over and over again

Specifc? how...pass pram (user_name) and make ur script genric one.

But yes I didnt notice, that He said he didnt have DBA Privilage as Jurij pointed me...

Abhay.

[jovery]

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
spool /tmp/drop_all.sql
Select 
  Rtrim('drop '||object_type||' '
    ||object_name||decode(object_type,'TABLE',' cascade constraints')
    || ';' )
from user_objects
order by object_name
/
spool off

set head on feedback on verify on

spool /tmp/drop_all.log

set echo on
@/tmp/drop_all.sql
spool off

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.

Regards