Delete all table in the schema with a single command

[sonuji_in]

Hi All,

I want to delete all the tables in my schema in a single command / function. I don't have DBA rights for this schema and I have only SQL Plus. I am working on :

Oracle9i Enterprise Edition Release 9.0.1.0.0 - Production
PL/SQL Release 9.0.1.0.0 - Production
CORE 9.0.1.0.0 Production
TNS for Solaris: Version 9.0.1.0.0 - Production
NLSRTL Version 9.0.1.0.0 - Production

Thanks in adv,

[davey23uk]

spool drop.sql
select 'drop table ' || table_name || ';' from tabs;
spool off

@drop

[jovery]

Run the following

SET HEADING OFF FEEDBACK OFF PAGES 0
SPOOL delete_table.sql
SELECT 'DROP TABLE '||table_name||';'
FROM user_tables;
SPOOL OFF

@delete_table

This is a very simplistic approach and you will probably need to disable any referential constraints.

HTH

[abhaysk]

iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?

[jovery]

Originally posted by sonuji_in
I want to delete all the tables in my schema

[abhaysk]

Jim :

I saw that he indeed wrote Tables, but what I guessed, seeing the way he posted, was he wants to flush all of the objects(Tables,Procs,Packages and so)...

Let him comment on it.

Abhay.

[sonuji_in]

Thanks for the co-operation and instant reply....

This procedure worked fine and I was able to delete all the tables :

spool drop.sql
select 'drop table ' || table_name || ';' from tabs;
spool off

@drop


but, I want to delete all tbe objects in the schema.

Thanks,,

[pando]

set termout on
set head off feedback off verify off termout off
spool /tmp/drop_all.sql
Select 
Rtrim('drop '||object_type||' '||owner||'.'
       ||object_name||decode(object_type,'TABLE',' cascade constraints')
       || ';' )
from dba_objects
where owner = upper('&owner')
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

[abhaysk]

I dunno why you guys wana do circus....isnt this enough?

Originally posted by abhaysk
iF you wana drop all objects in a schema, then Drop schema & Recreate it. Any Probs?

[pando]

well many times I cant be bothered to recreate users and grant all the privileges, roles, profiles