Space Management Question??

Hi there,

My site is running 7.3.4.3 on AIX 4.1.5. I was trying to create an index on date column in a quite large table (around 15,000,000 rows or 180MB) but failed and returned the following message:

ORA-01630: max # of extents num reached in temp segment in TS_INDEX01

The SQL statment tried to create that index is shown below:

create index idx_mytable on mytable (mydate) tablespace TS_INDEX01 pctfree 5 storage (initial 100M next 20M pctincrease 0) unrecoverable;

Since the size of the index trends to be greater than 140MB, setting "Initial Extent" to 100M to reduce extent usage.

According to the Oracle manual, the reason causing this error message is as follows:

1. The maximum amount of space allowed for saving undo entries has been reached for a temporary segment in the named tablespace
2. Space could not be allocated in the data dictionary to contain the definition of the object.

However, it seems not to fit into my case. I have a 900MB temporary tablespace (alter to "temporary" type) and TS_INDEX01 remains more than 1G by querying DBA_FREE_SPACES. Also the max. # of extents in TS_INDEX01 is 505 and used less than 50 by
querying DBA_SEGMENTS. Furthermore, there is no other process while creating that index.

At first, I guessed TS_INDEX01 could not allocate such large inital extent to that index due to a heavy fragmentation. But still no use after coalescing TS_INDEX01. Hopeless, my final workaround is to drop everything on TS_INDEX01 and re-create them on that again. Anyway, it works but plainful.

Please suggests me what should I do next time? And anyone can tell me what kind of mechanism Oracle uses to create an index?

Thanks in advance.

Dicky Received on Sat Dec 04 1999 - 20:26:46 CST