RE: Time taken to drop a schema

From: <>
Date: Mon, 1 Jun 2009 09:56:18 -0400
Message-ID: <>

Since this is on 10g, adding PURGE option will prevent dropped tables/indexes from going into recycle bin.

Drop table<table_name> cascade constraints purge;

Sent by: Jun 01 2009 09:49 AM
Please respond to

To, cc
RE: Time taken to drop a schema

Or, in one step.

select 'drop '||object_type||' "'||object_name||'"'||

      decode(object_type,'TABLE',' cascade constraints;',';')    from user_objects
   where object_type not in ('INDEX','TRIGGER','PACKAGE BODY','LOB')  order by object_id;

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
[] On Behalf Of Stephane Faroult Sent: Sunday, May 31, 2009 8:40 AM
To: Amihay Gonen
Subject: Re: Time taken to drop a schema

Something like this (call it drop_user_objects) should do in most cases. Run it under SQL*Plus from the account you wish to drop. It generates a drop_<SCHEMA_NAME>_objects.sql that you should inspect, then run from the same account. Afterwards connect as a DBA and drop the account.

store set myenv replace
set echo off
set autotrace off
set trimspool on
set pagesize 0
set timing off
set verify off
set recsep off
set feedback off
set termout off
col dummy noprint new_value account
select user dummy
from dual;
spool drop_&account._objects.sql
select 'alter table "' || table_name || '" drop constraint "' || constraint_name || '";'
from user_constraints
where constraint_type = 'R'
select 'drop ' || object_type || ' "' || object_name || '";' from user_objects

where object_type in ('TABLE', 'VIEW',
                      'SYNONYM', 'SEQUENCE',
                      'FUNCTION', 'PROCEDURE',

spool off
set feedback on


Amihay Gonen wrote:
> Can you send a example of the script ?. we've similar problem and it
will save me some time of coding this script.
> 10x
> -----Original Message-----
> From:
[] On Behalf Of Stephane Faroult
> Sent: Sunday, May 31, 2009 10:59 AM
> To:
> Cc: oracle-l
> Subject: Re: Time taken to drop a schema
> Ram,
> DROP USER CASCADE always takes a lot of time, I suspect that the
> checking of constraints wasn't very well coded in the first place, and
> as it's obviously not a major feature that customers use very often,
> they have never fixed it. I have always found that:
> 1) Querying the data dictionary to generate DROP statements for all FK
> constraints and running them
> 2) Generating DROP statements for all the objects that aren't
> automatically dropped when another one is dropped (no need to bother
> about indexes, triggers, etc.) and running them
> 3) running DROP USER on the empty schema
> is usually much faster.
> S Faroult
> Ram K wrote:
>> Hi
>> I dropped a schema that had about 175G of data in it. It took almost
>> 10 hrs for that to happen, Is this normal. The system is in
>> noarchivelog mode, v 10.2.
>> --
>> Ram.
> --

Stephane Faroult
RoughSea Ltd <>



Received on Mon Jun 01 2009 - 08:56:18 CDT

Original text of this message