Re: Problem with temp tablespace

Stan Brown wrote:

> I'm trying to build an index on a 7.3.4.5 system.
>
> I;ve run the following as user "sys":
>
> ALTER USER flink TEMPORARY TABLESPACE TEMP ;
> ALTER USER flink DEFAULT TABLESPACE TEMP ;

In the first place, it's a very strange piece of behaviour to want a user's default tablespace to be a temporary one. That means that if this user were to try to create a table or an index without explicitly requesting a tablespace for the new object, the create statement would fail, because you can't house permanent objects in temporary tablespace.

I suppose it's one way of *requiring* users to explicitly declare tablespaces at creation time, but it's not exactly normal practice.

>
> Yet, when i try to create an index as this suer, I get.
>
>
> ERROR at line 2:
> ORA-01630: max # extents (1) reached in temp segment in tablespace
> INDEX2_TS
>
>
> The _index_ is declared to be in IDEX2_TS, but this message, says I'm out
> of TEMP space.

>
> What's going on here?
>
> I think I have spce for this index in the specifed tablespace:
>
> TSPACE TOT_MB ORA_BLKS TOT_USED PCT_USED
> --------------- ---------- ---------- ---------- ----------
> CONFIG_TS 20 2560 190 7.42
> DATA_LOG_TS 30 3840 1360 35.42
> INDEX1_TS 8200 1049600 938125 89.38
> INDEX2_TS 8300 1062400 664940 62.59
> RBS_TS 1000 128000 25600 20
> SYSTEM 40 5120 1923 37.56
> TEMP 250 32000 11970 37.41
> TOOLS 15 1920 485 25.26
> TREND_TS 16500 2112000 1476030 69.89
> USERS 1 128
>
>
> Here's the index ceate statement:
>
> CREATE UNIQUE INDEX P_STA_PUMP4_2
> ON P_STA_PUMP4
> (
> DSTAMP
> )
> TABLESPACE INDEX2_TS
> STORAGE (
> INITIAL 79000K
> NEXT 0K
I have to say that this is one of the oddest storage clauses I've seen in a long, long time, too. All extents within a tablespace should be of a consistent size, and it is utterly daft to try and prevent growth of an object by setting a next size of zero. You go on to prevent growth by setting maxextents to 1, so your job is done at that point. But maxextents can be altered (perhaps not by you), and if that were ever to happen, you've set yourself up with a mess.

Why are you trying to get this index into a single extent anyway? There's no performance nor manageablility benefits to doing so.

Were you to set this storage clause to something rather more sensible, and have equal initial and next sizes, and a maxextents of, say 20-30, you would, I think, find your error message disappearing.

> MAXEXTENTS 1
This is, in fact, the proximate cause of your error message. The index obviously wants an additional extent, and you're not allowing it to acquire it.

> PCTINCREASE 0
> )
> PCTFREE 1;
>

Why on earth are you setting pctfree to 1 for this index? Either it should be zero, or something appropriate, and I'll lay odds that 1 is not appropriate. The role of pctfree in an index is to attempt to stop block splits due to subsequent insertion of values which fit between existing values. It has no other role in an index, because index entries do not, ever, grow in size (which is what pctfree is there for on tables). An update of a row of the table, necessitating the growth of an entry in the index, causes the original index entry to be deleted and a completely new one to be inserted. Therefore, no update actually happens, and therefore no space needs to be wasted to allow it to happen.

What is more, your index is on a column called DSTAMP, which I'm guessing is a date stamp, and it's unique. So I'm also guessing that the dates being indexed are forever increasing: you'll never insert a new record in the table which gets a date stamp earlier than a previosu record. In which case, PCTFREE should be zero, and you should have done with it.

If pre-insertions are going to take place, then 1 is highly unlikely to do much of a job of preventing block splits. The default's 10 for good reason.

In short, there's a lot more going on here than merely the inability to acquire an extent for the creation of an index. There's, er, 'unusual' user setups, a weird desire to fit things into single extents, and an apparent misunderstanding of the role of pctfree in indexes. I'd get all those things sorted first, and you'll find error messages like these just go away of their own accord.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------