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

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 08 May 2004 10:32:58 +1000
Message-ID: <409c2ab0$0$10520$afc38c87@news.optusnet.com.au>

Mark Bole wrote:

> Howard J. Rogers wrote:
>

>> Marck wrote:
>>
>>> Hi Gurus,
>>>
>>> I have a questions about this error.
>>>
>>> I have this conf
>>>
>>> CREATE TABLESPACE ts1_data
>>>     DATAFILE '/d00/oradata/ts1_data.dbf'  size 118M
>>>     AUTOEXTEND ON   NEXT 59M    MAXSIZE 236M;
>>
>>

> [...]
>

>>
>> Crikey. Autoextend is a bad idea. But incrementing in steps of 59M and 
>> stopping at 236M is even whackier. This is fine-tuning space 
>> management gone mad (IMHO, natch).
>>

> [...]
>

>>
>> With an up-to-date 8i, there's no excuse: dictionary managed 
>> tablespace is ancient history, and you should be using LMT.
>>
>> Regards
>> HJR

>
>
> Amen to LMT.
>
> But why do you keep on stating without qualification that "autoextend is
> a bad idea".

What do you mean "without qualification"? I've said it's fine for SYSTEM. And I have repeatedly said "but if you must use it, make sure you have NEXT and MAXSIZE set". Those are qualifications.

> I have been using autoextend on many datafiles in many
> types of database (production, training, test, development) for
> different applications for a long time and have not had any problems, so
> I wonder what I'm missing?

If you have autoextend on, *when* does the data file grow in size? When a segment needs more space, that's when. Why would a segment need more space? Because some poor user is trying to insert into it, that's why. Ergo, the data file will extend when a user is trying to perform DML.

Can you begin to see why autoextend is not such a good idea?

If you use autoextend AT ALL, *some* of your DML will encounter waits as the autoextension is arranged for you. If you use autoextend without a large NEXT, then *a lot* of your DML will encounter those waits.

> This is especially hard to reconcile with the repeat claims by others
> that "disk is cheap" and you shouldn't be concerned about limiting the
> size of your UNDO tablespace (which implies you should have AUTOEXTEND
> on for that one?).

Oh dear. UNDO tablespace should never, ever, ever be autoextend. An unnecessarily large undo tablespace is an extremely heavy hitter in terms of performance degradation.

> Now, "MAXSIZE UNLIMITED" is a bad idea, of course (and unfortunately
> that's the default for Oracle-managed files when no SIZE is specified).
> But how is judicious use of AUTOEXTEND any different than carrying a
> spare tire in your car or an extra piece of currency tucked away in your
> wallet?

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 Received on Fri May 07 2004 - 19:32:58 CDT