Re: Problem with temp tablespace

Stan Brown wrote:

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

Then it shouldn't be.

>I'm not even certain that 7.3.4.5 even
> has the concept of temoporary tablespaces.

Of course it does. They were invented in 7.1.

But if you don't believe me, simply type:

create tablespace temp99 datafile '/bing/bang/bong/temp99.dbf' size 5m temporary;

...and report back on what happens.

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

So? It's still a shockingly bad piece of user creation syntax, and your comments about TEMP neing a permanent tablespace just make things worse!  

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

Well, have you considered that the reason they work is that they only ever asked for a single extent?? MAXEXTENTS 1 is not intrinsically evil. But it makes no sense if the index needs to acquire more than one extent, does 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.
>
> It's historical. But I'm beting thta at one time I tried zero and it was
> not allowed.

I'm betting that that's not true. Because PCTFREE 0 is a perfectly legitimate setting for an index -any index- and would suit your index needs just fine.  

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

Therefore, PCTFREE should be zero.

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

Not really! TEMP should be temporary. User's shouldn't have TEMP as their default tablespace. PCTFREE for indexes on monotonically incrementing sequences (of which your DSTAMP is an example) should have zero pctfree. Extent sizes should be consistent within a tablespace, otherwise you'll have fragmentation problems like you wouldn't believe. And MAXEXTENTS 1 is, er, shall we say "ambitious", particularly when the index concerned *needs* more than one extent. And none of that is particularly new, nor version-dependent, but merely good DBA practice and commonsense.

If you've inherited a database that has been created in a completely whacky way as this one seems to have been, then it's time to progressively implement new, better and more intelligent practices, not continue to adopt the madness of the past.

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