Re: Time taken to drop a schema

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Sun, 31 May 2009 14:39:32 +0200
Message-ID: <4A227A84.2080508_at_roughsea.com>

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',
                      'PACKAGE');

spool off
_at_myenv
set feedback on

HTH SF

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: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephane Faroult
> Sent: Sunday, May 31, 2009 10:59 AM
> To: lambu999_at_gmail.com
> 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.
>
> HTH
>
> 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.
>>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>

--
http://www.freelists.org/webpage/oracle-l

Received on Sun May 31 2009 - 07:39:32 CDT