Re: looking for a a faster way to drop a schema

I am fairly certain that 'drop user cascade' is NOT recoverable and is NOT written to the redo logs or rollback tablespaces.

I take part of that back. I still believe it is NOT recoverable, but there may be a fair amount of redo activity.

My guess is that the RDBMS is spending its time:

1. Doing queries determining which objects to drop.
2. Locking objects.
3. Returning storage to the database.

Remember, the server processes (lgwr,dbwr,etc.) do not deal with rows of data, but instead blocks of data. If you drop a table, the RDBMS must still update the Oracle equivalent of a DOS FAT table. If there are a lot of extents in the tables (and indexes), this can take some time.

If I had some time on my hands, I'd try drop user cascade with fragmented and non-fragmented tables to see what the difference is. That might be interesting.

Sheilah Scheurich wrote:
>
> It may not be the command that is the problem, It may be where your
> rollback tablespace and redo logs are located. You may be getting
> into some IO contention. Check to see that they are not on the same
> disk as your data and indexes, and are separate from each other.
>
> -Sheilah Scheurich
> DBA
> scheuric_at_sprynet.com

Will.

-- 
======================================================================
Will Kooiman                        Computer Systems Authority
Systems Consultant                  6380 LBJ Freeway, Suite 181
(972) 960-0180 x236                 Dallas, TX 75240
mailto:wkooiman_at_csac.com            http://www.csac.com