Re: About ORA-01658 unable to create INITIAL extent for segment in tablespace <ts>

From: Mark Bole <makbo_at_pacbell.net>
Date: Mon, 10 May 2004 15:24:23 GMT
Message-ID: <H8Nnc.65688$6X4.8545@newssvr25.news.prodigy.com>

Howard J. Rogers wrote:

> Mark Bole wrote:
>

>> Howard J. Rogers wrote:
>>
>>> Marck wrote:
>>>
>>>> Hi Gurus,
>>>>[...]

> Because however judiciously you use it, *some* DML will suffer for it.
> Does that mean it is a no-no? No, it means it's a question of costs v.
> benefits. If you don't have time to manage your file space allocations
> proactively and carefully, then autoextend is a very convenient
> alternative. But don't ever pretend that Oracle provides such goodies
> for free: they always come with (usually hidden) costs.
>

>> You don't plan to use it, but it's very convenient to get through the 
>> unexpected emergency so you have more time to deal with the underlying 
>> problem.

>
>
> I have used it when I go on holiday. I would rather the database keep
> working, however poorly, than that I get rung up when I'm on the beach.
> See: it's a convenience thing. But there are performance implications
> from having all that convenience.
>

>> I haven't tried resumable statements -- I suppose they are an 
>> alternative, but with more drawbacks than AUTOEXTEND (as in, you have 
>> to explicitly set them up, and they still don't allow an operation to 
>> complete until there is some intervention).

>
>
> Strangely, I thought that's why we had DBAs, to intervene.
>
> Regards
> HJR
That answers my question. No disagreement here that it's a cost vs. benefit decision. Just because I have autoextend on doesn't mean I don't take steps to prevent it from happening very often.

It reminds me of a script that ran daily on an Oracle 6 database I worked with. It saved and reported a six-month history of tablespace growth, determined whether or not any table or index segment would be unable to allocate its next extent based on an analysis of contiguous free space and pct_increase, and even had an option to eliminate "honeycombs". All we had to do was remember to actually look at the report every day and intervene when indicated!

Nowadays I can just run "Tablespace Analysis" in OEM. If we purchased the OEM option packs and spent the time to set up the various space management event tests, I could be back just about where I was 12 years ago ;-)

--Mark Bole Received on Mon May 10 2004 - 10:24:23 CDT