ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2

[anujpathak]

When i create a table oracle gives below mention error

Though there is enough space available in the data file of USERS2 tablespace.

I have oracle8i workgroup on windows-NT

datafile status of USERS2 tablespace from enterprise manager
size (M) used(M)
data1.dbf 650 512.932
data2.dbf 500 272.018 <-enough space here
data3.dbf 500 431.406 <-enough space here


create table abc
(acno char(1) )
tablespace users2 storage (initial 1m)

ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2

What could be the solution of this problem ?????
Thanks in advance.

from Anuj Pathak
Hero Financial Services Ltd.

[jmodic]

It seems your tablespace is realy badly fragmented! It is not sufficient to have enough free space in the datafile, this space has to be *contiguous*. Run the following query to see sizes of your free chunks in that tablespace:

select file_id, bytes as free_chunk from dba_free_space
where tablespace_name = 'USERS2'
order by bytes desc;

Try also coalescing adjascent free chunks in bigger chunks by
ALTER TABLESPACE ZSERS2 COALESCE;

HTH,

[anujpathak]

hi jmodic

I tried yr suggestions performed steps and output i am giveing u below.

it creates the temp table with single field and initial extend 20k
but actual table like fdscheme with initial 20k does not create

Is there any other way to solve tablespace fregmentation

from Anuj Pathak
Hero Financial Services Ltd.


SQL> alter tablespace users2 coalesce ;

Tablespace altered.

SQL>
SQL> select file_id, bytes as free_chunk from dba_free_space
2 where tablespace_name = 'USERS2'
3 order by bytes desc;

FILE_ID FREE_CHUNK
--------- ----------
12 104857600
17 104855552
8 90478592
12 68157440
15 68157440
8 53114880
12 52424704
12 13617152
15 2713600
15 1054720
8 102400
8 30720

12 rows selected.

SQL> create table abc (acno char(7)) tablespace users2 storage (initial 20k);

Table created.

SQL> CREATE TABLE FDSCHEME (
2 SCHEMECD CHAR(3) CONSTRAINT PK_SCH PRIMARY KEY,
3 SCHEMENAME VARCHAR2(30) NOT NULL,
4 INTRATE NUMBER(5,2),
5 COMPOUND NUMBER(1),
6 IP_MON NUMBER(2),
7 PERIOD_S NUMBER(2) NOT NULL,
8 PERIOD_E NUMBER(2) NOT NULL,
9 USERID NUMBER(3),
10 CRE_DT DATE
11 )
12 tablespace users2 storage (initial 20k)
13 /
CREATE TABLE FDSCHEME (
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2

[jmodic]

THis realy is a strange one. Sorry, I have no idea what could be wrong here. Anyone else?

[anujpathak]

If i do the following will it work


1. export of the tablespace
2. drop all the tables from the tablespace
3. import the exported dmp file again

from Anuj
Hero Financial Services Ltd.

[rborikar]

Have you tried by decreasing the initial extent value? But make sure that it is quite enough to hold the data. I faced the same problem , then I calculated the exact initial extent size required and it works.

Thanks,

Rajesh

[sonaliak]

Is it OK to change the size of extents in the existing tablespace ?

I have a export dmp which I want to import to my tablespace.
But I want to use my own extent sizes and not the one from the dmp. Is there any way to do this ??

[sonaliak]

I forgot to mention I want to reduce the size of extents.

[pando]

Hi

you can import with rows=n
then alter the storage parameters, if you have many objects then you probably better build a script with dynamic sql to do the alter table/index etc etc.
after just import again with ignore=y

[rsuri]

a long shot but you may want to run DBV (DB Verify) to see if there is a corrupt block on the disk.
I think DB should be shutdown in order to run DBV

- Rajeev