About ORA-01658 unable to create INITIAL extent for segment in tablespace <ts>
Marck
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;
CREATE TABLESPACE ts1_INDEX
DATAFILE '/dxx/oradata/ts1_index.dbf' size 30M
AUTOEXTEND ON NEXT 15M MAXSIZE 60M;
And this tables and indexes
CREATE TABLE T1 (
id_t1 NUMBER NOT NULL, eS NUMBER NULL, eD NUMBER NULL,
PRIMARY KEY (id_t1)
USING INDEX
PCTFREE 10
TABLESPACE ts1_INDEX
STORAGE ( INITIAL 14k NEXT 14k
MINEXTENTS 1 MAXEXTENTS 249 )
)
PCTFREE 10
TABLESPACE ts1_DATA
STORAGE ( INITIAL 20K NEXT 20K
MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 0 );
CREATE TABLE T2 (
id_t2 NUMBER NOT NULL, e VARCHAR2(10) NULL,
te VARCHAR2(70) NULL,
PRIMARY KEY (id_t2)
USING INDEX
PCTFREE 10
TABLESPACE ts1_INDEX
STORAGE ( INITIAL 1K NEXT 1K MINEXTENTS 1
MAXEXTENTS 250 ))
PCTFREE 10
TABLESPACE ts1_DATA
STORAGE ( INITIAL 4K NEXT 4K MINEXTENTS 1
MAXEXTENTS 50 PCTINCREASE 0)
CREATE TABLE T3(
id_t3 NUMBER NOT NULL, id_t NUMBER NULL,
id_t1 NUMBER NOT NULL, t VARCHAR2(255) NOT NULL,
d VARCHAR2(2000) NULL, fi DATE NULL,
fe DATE DEFAULT NULL, ln VARCHAR2(50) NULL,
fn VARCHAR2(100) NULL, fs NUMBER NULL,
b NUMBER(1) NULL, p NUMBER(1) NULL,
PRIMARY KEY (id_t2)
USING INDEX
PCTFREE 10
TABLESPACE ts1_INDEX
STORAGE ( INITIAL 18k NEXT 18k MINEXTENTS 1
MAXEXTENTS 249),
FOREIGN KEY (id_t1) REFERENCES T1)
PCTFREE 10
TABLESPACE ts1_DATA
STORAGE (
INITIAL 267K NEXT 267K MINEXTENTS 1
MAXEXTENTS 249 PCTINCREASE 0)
The storage values where calculated following the 'ORACLE DBA
HandBook'. Theoricaly I have other tables and indexes, and the sum of
the space in the INITIAL of ALL tables is the space asign to the
tablespaces (data and index), but when i try to create, I just only
create the tables above, the last table have other index,
CREATE INDEX XIF29T3 ON T3
( id_t3 ASC, id_t2 ASC )
PCTFREE 10
TABLESPACE UTM_ST_INDEX
STORAGE ( INITIAL 25k NEXT 25k MINEXTENTS 1
MAXEXTENTS 249);
when i try to create it the error ORA-01658 appears.
Checking all yours messages found that the problem is out of space,
but the sum of the INITIAL is minimum to this tables and indexes.
When i check the space in the server i saw that the ts1_data is in 101
mb aprox and the ts1_index is in the max size (60M), and checking
the DBA_FREE_SPACE the free space for ts1_INDEX is less than 30MB and
the ts1_DATA is 140MB more or less.
Why the error appears ? then the INITIAL is not the only condition for
creating the index (or table ?, after two more tables, the ts1_DATA is
out of space too)
or the free space must be INITIAL + NEXT * (MAXEXTENTS - 1)? is that
is the case, we try to calculate that but the number differs very
much...
any suggestions ?
My Oracle is the 8i and the server is AIX
Howard J. Rogers
> 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).
And no default storage clause. And no minimum extent clause either. Sad,
sad, sad.
[snip]
So you immediately have two different extent sizes inside TS1_DATA, and
you won't get a 4K initial anyway (minimum allocation for initial is 5
Oracle blocks).
> CREATE TABLE T3(
> id_t3 NUMBER NOT NULL, id_t NUMBER NULL,
> id_t1 NUMBER NOT NULL, t VARCHAR2(255) NOT NULL,
> d VARCHAR2(2000) NULL, fi DATE NULL,
> fe DATE DEFAULT NULL, ln VARCHAR2(50) NULL,
> fn VARCHAR2(100) NULL, fs NUMBER NULL,
> b NUMBER(1) NULL, p NUMBER(1) NULL,
> PRIMARY KEY (id_t2)
> USING INDEX
> PCTFREE 10
> TABLESPACE ts1_INDEX
> STORAGE ( INITIAL 18k NEXT 18k MINEXTENTS 1
> MAXEXTENTS 249),
> FOREIGN KEY (id_t1) REFERENCES T1)
> PCTFREE 10
> TABLESPACE ts1_DATA
> STORAGE (
> INITIAL 267K NEXT 267K MINEXTENTS 1
> MAXEXTENTS 249 PCTINCREASE 0)
And this is just getting crazy. 267K??? Why not 266? or 268?
> The storage values where calculated following the 'ORACLE DBA
> HandBook'.
Presumably a reference to Kevin Loney's masterpiece? Trash it. Indeed,
trash every book you've got from Oracle Press unless it also happens to
have the magic words 'Tom' and 'Kyte' on the front.
[snip]
> any suggestions ?
Can I suggest you get a lot more relaxed about storage than you appear
to be? You're running 8i, so you should be using locally managed
tablespaces in any case, not the dictionary-managed monsters you've
produced here. If your application vendor says 'we don't support LMT',
then dump the vendor and find one that does, and in the meantime at
least implement default storage clauses and minimum extent clauses.
Why? Because the way you've got it at the moment, you are going to
suffer from tablespace fragmentation. And you are already suffering from
the primary drawback of dictionary-managed tablespace, which is a
propensity to, and a feeling that one must, fine-tune space management
to the nth degree.
Switch to locally managed tablespaces and you can never fragment it (or
at worst, with autoallocate, very,very rarely fragment it), and more
importantly, you can stop worrying about extent sizes and extent
numbers. (New article just up at www.dizwell.com in the Basic Admin FAQ
explains this in some detail).
Just create a couple of locally-managed autoallocate tablespaces, and
forget all about the extent sizing stuff. Then you can move on and
concentrate on matters which really do have a bearing on performance.
> My Oracle is the 8i and the server is AIX
With an up-to-date 8i, there's no excuse: dictionary managed tablespace
is ancient history, and you should be using LMT.
Regards
HJR
Mark Bole
> 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
Daniel Morgan
> 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?
The fact that you are not paying attention to what is going on. Or if
you are ... then you didn't need it in the first place.
--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)
Howard J. Rogers
> 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
Mike Ault
Oh boy, sone great quotes in here, if I follow the Foote/Rogers rules,
for a "Silly Things Experts Say" seminar...I can even attach names to
them...
Mike
Daniel Morgan
> Oh boy, sone great quotes in here, if I follow the Foote/Rogers rules,
> for a "Silly Things Experts Say" seminar...I can even attach names to
> them...
>
> Mike
Just a few posts ago you wrote:
"I don't mind when folks disagree, however, when you attach names to
quotes and then say in effect this person is an idiot and this is
rubbish, it does appear to be a personal attack rather than a
professional one."
Which is pretty much exactly what you seem to have done above.
Everyone ... lets see if we can't achieve a higher standard of
discourse. If you wish to throw around words like "idiot" and "silly"
please do so off-line.
Thank you.
Howard J. Rogers
> Oh boy, sone great quotes in here, if I follow the Foote/Rogers rules,
> for a "Silly Things Experts Say" seminar...I can even attach names to
> them...
>
> Mike
Well, I'm flattered you would call me an expert, but don't keep us all
in the dark, Mike. Show us the list of silly things said in that post.
HJR
Marck
Thanks for the advice and discussion for the autoextent subject, but,
muy question continue without answer.....
The MaxExtent is because i have a limit. Sad but true =S, well.
so
Why a creation of a table and a index is taking too much space that
the initial ?=...
ahd really what is the operation for estimating that space ?
"Howard J. Rogers" <h...@dizwell.com> wrote in message news:<409d7b14$0$442$afc3...@news.optusnet.com.au>...
Mark Bole
> 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
Mark Bole
> Hi Gurus,
>
> Thanks for the advice and discussion for the autoextent subject, but,
> muy question continue without answer.....
>
> The MaxExtent is because i have a limit. Sad but true =S, well.
>
> so
> Why a creation of a table and a index is taking too much space that
> the initial ?=...
>
> ahd really what is the operation for estimating that space ?
>
>
[...]
The advice to use locally managed tablespaces with appropriate options
is still by far your best solution. But if you just want to resolve the
error you're getting, go no further than the following documentation
found at http://tahiti.oracle.com. Note the mention of contiguous
(unfragmented) space.
"ORA-01658 unable to create INITIAL extent for segment in tablespace string
"Cause: Failed to find sufficient contiguous space to allocate
INITIAL extent for segment being created.
"Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space
to the tablespace or retry with a smaller value for INITIAL."
--Mark Bole