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. >> > > -- > //www.freelists.org/webpage/oracle-l > > > > -- Stephane Faroult RoughSea Ltd <http://www.roughsea.com> -- //www.freelists.org/webpage/oracle-l