Re: Problem with temp tablespace

In <3f846668$0$26662$afc38c87_at_news.optusnet.com.au> "Howard J. Rogers" <hjr_at_dizwell.com> writes:

>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.

TEMP _is_ a persistent tablespace. I'm not even certain that 7.3.4.5 even has the concept of temoporary tablespaces. Second the "user" is really a control system. noNo one will ever be using this user ID interactively, other than me as the system administrator.

>> 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.

Interesting. I've got about 100 other indexes with identical storage clauses that _do_ work.

>> 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.

It's historical. But I'm beting thta at one time I tried zero and it was not allowed.

>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.

Agreed, and all corect assumptions. See above.

>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.

K, I'll look inoto that.

It's a pretty unusual application. It's really data storage fof a control system. All tables are data + a timestamp, and a datestamp. New records are added ever 15 seconds, and a daily procdure removes the oldest records to maintain room in fixed size allocation tables/indexes.

Does it make a bit more sense now?

-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
						-- Benjamin Franklin