| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Space Management Question??
ccdicky wrote:
>
> 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
The sizing on your index is OK - but to build that index, the database has to sort the rows, and it is the SORTING mechanism that is running our of extents.
Look at the default sizing for your temporary tablespace for that user, ie
select * from dba_tablespaces
where tablespace_name in (
select temporary_tablespace
from dba_users
where username = ... )
Its that tablespace that will need adjustment of extent sizes and possibly its total size.
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Sun Dec 05 1999 - 03:35:18 CST
 |
 |