Re: DROP TABLE always takes long time.

I had a problem like this with IOT's in Oracle8 (Index-Organized tables). The problem turned out to be too many allocated segments in my DB. Actually it was the freed segments that were the problem. To do certain tablespace functions it had to iterate through 30,000 x 30,000 x 30,000 freespace entries in dba_segments (I think that's it). Because the PCTINCREASE parameter was 0, Oracle never coalesced this space together. I just did an ALTER TABLESPACE x COALECE; and all was fine. I also dropped the problem tables and reloaded them.

Scott Johnson wrote:

> Why does drop table generate rollback when a DDL command automatically
>
> does a commit. You cannot rollback a drop table. Right?
>
> Scott
>
> draggs_at_hawkeye.idx.com wrote:
>
> > On Tue, 13 Jan 1998 17:08:39 GMT, chuckh_at_dvol.com (Chuck Hamilton)
> > wrote:
> >
> > >What would cause all DROP TABLE commands, even on the simplest of
> > >tables, those with no indexes, rows, or constraints, to take an
> > >inordinately long time?
> > >
> > >For example:
> > >
> > > CREATE TABLE mytable (
> > > name VARCHAR2(10),
> > > qty NUMBER(9,0)
> > > );
> > >
> > >takes about 2 seconds. But
> > >
> > > DROP TABLE MYTABLE;
> > >
> > >takes 3.5 minutes;
> >
> > Try truncating the table before you drop it. Truncate does not
 right
> > the the rollback segments, but drop does. It can really speed
 things
> > up if theres much data in the tables.
Received on Sun Jan 18 1998 - 00:00:00 CST