Re: Dropping Tables - Slow Performance

Hi Jeanne,

One possible factor is the tables have been stored scattered in the database, ie consist of multiple extents. This will force Oracle to execute extent management during the drop table command. Extent management has always been very expensive in terms of resource.
One command to verify this:
select segment_name, bytes, extents
from user_segments ;
(assuming you log in as owner of the tables) If all the tables are in one tablespace, it would be possibly faster to export this user (this doesn't export the create user command, beware), drop the user and recreate it from scratch as this will defragment the tablespace at the same time.

Hth,

Sybrand Bakker, Oracle DBA

Jeanne wrote:

> I am running scripts that drop numerous tables in a database for a
> software upgrade. It is taking up to a minute just to drop a table. Our
> DBA just shrugs and goes and gets coffee. None of the tables has any
> constraints, other than explicitly created indexes, and we don't maintain
> histograms.
> Anyone have any ideas on where I could look?

Received on Sat Jan 30 1999 - 00:47:18 CST