Re: How to drop all of a user's data?

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 22 Jul 2006 07:21:40 +0200
Message-ID: <44c1b5e3$0$29873$626a54ce@news.free.fr>

<andrew.fabbro_at_gmail.com> a écrit dans le message de news: 1153514221.715473.214990_at_s13g2000cwa.googlegroups.com...
|I have user joeschmo in a 10g database. I have a script that creates a
| bunch of tables, sequences, packages, etc. under that user (called
| joeschmo.table1, etc.)
|
| How can I delete all the tables, sequences, packages, etc. for user
| joeschmo - without deleting the user? I can do a lot of drop table x,
| drop sequence y, but I was hoping for something in a single statement.
|
| I'm probably missing something obvious...
|

set heading off
set pagesize 0
set feedback off
set timing off
set time off
set trimspool on
set trimout on
set linesize 100
set echo off
spool t
select 'drop '||object_type||' '||object_name||

       decode(object_type,'CLUSTER',' including tables cascade constraints',
                          'TABLE',' cascade constraints',
                          '')||';'

from user_objects

where object_type in ('CLUSTER','CONTEXT','DATABASE LINK','DIMENSION',
                      'DIRECTORY','FUNCTION','INDEX TYPE',
                      'JAVA','LIBRARY','MATERIALIZED VIEW','OPERATOR',
                      'OUTLINE','PACKAGE','PROCEDURE','SEQUENCE',
                      'SYNONYM','TABLE','TYPE','VIEW')

order by object_type, object_name
/
spool off
@t.lst

Regards
Michel Cadot Received on Sat Jul 22 2006 - 00:21:40 CDT