ORA-01658: unable to create INITIAL extent for segment in tablespace USERS2
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    33

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

    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.



  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Nov 2000
    Posts
    33
    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


  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    THis realy is a strange one. Sorry, I have no idea what could be wrong here. Anyone else?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Nov 2000
    Posts
    33
    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.

  6. #6
    Join Date
    Nov 2000
    Posts
    48
    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

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    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 ??
    Sonali

  8. #8
    Join Date
    Jan 2001
    Posts
    318
    I forgot to mention I want to reduce the size of extents.
    Sonali

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  10. #10
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width