RE: Time taken to drop a schema

From: <Mayen.Shah_at_lazard.com>
Date: Mon, 1 Jun 2009 09:56:18 -0400
Message-ID: <OF746DB3AC.6633954B-ON852575C8.004C4ED7-852575C8.004C90D2_at_lazard.com>

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;

HTH
Mayen

Joel.Patterson_at_crowley.com
Sent by: oracle-l-bounce_at_freelists.org Jun 01 2009 09:49 AM
Please respond to
Joel.Patterson_at_crowley.com

To
sfaroult_at_roughsea.com, Amihay.Gonen_at_ecitele.com cc
oracle-l_at_freelists.org
Subject
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-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephane Faroult Sent: Sunday, May 31, 2009 8:40 AM
To: Amihay Gonen
Cc: ORACLE-L
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',
                      '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


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






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

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