Hi all,

I was trying to update my schema on Oracle when I got the following error
message.
"ORA-01658: unable to create INITIAL extent for segment in tablespace
ACCOUNT".
On googling I read that it meant that there was no free extent in the
tablespace system greater than or
equal to the extent size I was asking for.

The ways it suggested me -
1.Add a datafile.
2.Resize the datafile if space available.

Could anyone please explain me the problem more precisely and how to go
about
following either of the above steps (with their merits).

Thanks in advance,
SK

if your are loading the data from an exp file and you used compress
option which I suggest is still the default then every table is
reorganized to one large extent.
if your then are importing the data every tables needs at least its
initial extent satisfied in one chunk.
To avoid this you can either exp with compress no or create the table
account in advance with a smaller extent and then import it.
the other actions that can be taken are already known to you while you
searched google.
You have to answer to following questions:
growing tables are always an issue and all the fragments will be needed
in the near future.
if there are limits in the filesystem you probably will add a new
datafile at another mountpoint ...

--
Posted via http://dbforums.com

First; You provide no info about Oracle Version or if You have LMT or DMT
....

"Sharad K" <sk@sk.com> schrieb im Newsbeitrag
news:bha060$vvj6o$1@ID-192448.news.uni-berlin.de...
> Hi all,
>
> I was trying to update my schema on Oracle when I got the following error
> message.
> "ORA-01658: unable to create INITIAL extent for segment in tablespace
> ACCOUNT".
> On googling I read that it meant that there was no free extent in the
> tablespace system greater than or
> equal to the extent size I was asking for.
>
> The ways it suggested me -
> 1.Add a datafile.
> 2.Resize the datafile if space available.
>
Anyway,

first check out for free spaces:

select TABLESPACE_NAME, round(sum(BYTES)/1024/1024) "MB free"
from dba_free_space
where tablespace_name = 'ACCOUNT'
group by TABLESPACE_NAME;

TABLESPACE_NAME MB free
------------------------------ ----------
ACCOUNT 0 (maybe)

1 Zeile ausgewählt.

If you have this case, then Your TS could not take the load of more schema
objects, it is full.

So, if Your TS is in fact too small
-> try resize first to the max. file size of the OS, and if reached, only
then add a datafile
,
or Your intial extents clause is sized too big
-> make it smaller (and use uniform extent sized LMTs).

> Could anyone please explain me the problem more precisely

You just seem to need space ... go into the concepts manual about storage
priciples in Oracle.
->http://tahiti.oracle.com, search for "Tablespaces, Segments, Extents,
Blocks"

hth, Jan

"Jan Gelbrich" <j_gelbrich@westfalen-blatt.de> wrote in message
news:bha965$vua42$1@ID-152732.news.uni-berlin.de...
> First; You provide no info about Oracle Version or if You have LMT or DMT
> ...
Oracle 8.1.7 LMT

"Stephen_CA" <stephen.bell@sympatico.ca> wrote in message
news:5aeee507.0308120453.5e223449@posting.google.c om...
> "Sharad K" <sk@sk.com> wrote in message
news:<bha060$vvj6o$1@ID-192448.news.uni-berlin.de>...
> > Hi all,
> >
> > I was trying to update my schema on Oracle when I got the following
error
> > message.
> > "ORA-01658: unable to create INITIAL extent for segment in tablespace
> > ACCOUNT".
> > On googling I read that it meant that there was no free extent in the
> > tablespace system greater than or
> > equal to the extent size I was asking for.
> >
> > The ways it suggested me -
> > 1.Add a datafile.
> > 2.Resize the datafile if space available.
> >
> > Could anyone please explain me the problem more precisely and how to go
> > about
> > following either of the above steps (with their merits).
> >
> > Thanks in advance,
> > SK
>
> Hi,
>
> Oracle allocates space for segments (including tables) in units known
> as extents. The size of these extents is specified either when you
> create objects within a tablespace, when you create the tablespace
> itself, or using defaults.
>
> If your initial extent size was specified as, for example, 500 M, but
> your tablespace only holds 100M, it will not be possible to allocate
> the space for the initial extent..methods 1 and 2 that you've listed
> above will allow you to increase the size of the tablespace..to either
> resize a datafile or add a new one, use the ALTER TABLESPACE command.
> You can also recreate the table with a smaller initial extent if that
> is appropriate for your situation.
>
> I hope this helps,
>
> Steve

Hi Steve,
Thanks for the reply. Your description made the picture clear to me.
I could rectify the error I was getting.
Thanks again,
SK