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

From: Mark Bole <makbo_at_pacbell.net>
Date: Fri, 07 May 2004 22:45:47 GMT
Message-ID: <vkUmc.62038$oD5.17972@newssvr25.news.prodigy.com>

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

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

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

--Mark Bole Received on Fri May 07 2004 - 17:45:47 CDT