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',
                      'PACKAGE');
spool off
@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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Stephane Faroult
> Sent: Sunday, May 31, 2009 10:59 AM
> To: lambu999@xxxxxxxxx
> 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

Other related posts: