Re: Remove everything from a schema

From: Álvaro G. Vicario <>
Date: Fri, 28 Nov 2008 12:09:18 +0100
Message-ID: <ggojgo$af4$>

Michel Cadot escribió:
> I use the following script to drop all my objects:
> 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',
> 'OPERATOR', ' force',
> 'TABLE',' cascade constraints',
> 'TYPE', ' force',
> 'VIEW',' cascade constraints',
> '')||';'
> from user_objects
> where object_type in ('CLUSTER', 'CONTEXT', 'DATABASE LINK', 'DIMENSION',
> order by object_type, object_name
> /
> spool off
> @t.lst
> purge recyclebin;

This is not a complement to Charles' solution but an alternative, isn't it? (Running "DROP USER schema CASCADE" would be an option but it implies contacting the DBA.)

The production box will be using a bare Instant Client so I prefer not to rely on SQL*Plus commands but I get the idea. I've run the commands generated by your SELECT query and everything is happily gone. I can easily stuff it in a PHP script (or just run it manually).

Thank you!

-- - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web:
-- Mi web de humor al baño María:
Received on Fri Nov 28 2008 - 05:09:18 CST

Original text of this message