HELP: ORA-01653 Unable to extent a table in a tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: HELP: ORA-01653 Unable to extent a table in a tablespace

Hybrid View

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    HELP: ORA-01653 Unable to extent a table in a tablespace

    Hi friends, I have a question that I really need to solve. Please help me, any answer that can explain me what is happening will be very appreciated.

    Recently I performed an import operation that succesfully created all the tables and rows of the schema Y. The information was imported using the option compress=n. At the moment I don't know if this is relevant to the problem, but I think is a good idea tell you what I have done. After that, the users have been using the database without any problems (using DML sentences), but in this moment I'm receiving an error from one application that is trying to insert some rows. The error is:

    UNABLE TO EXTENT TABLE Y.X BY 5 IN TABLESPACE Z;

    I think the problem is the segment of the table cannot extent anymore.

    So I get these information from Oracle:

    1) BLOCK SIZE:

    show parameters db_block_size;

    NAME TYPE VALUE
    ------------------------------------ ------- -----------
    db_block_size integer 4096


    2) STORAGE OF TABLE:

    select * from dba_tables where table_name='X'

    OWNER: Y
    TABLE_NAME: X
    TABLESPACE_NAME: Z
    CLUSTER_NAME:
    IOT_NAME:
    PCT_FREE: 10
    PCT_USED:
    INI_TRANS: 1
    MAX_TRANS: 255
    INITIAL_EXTENT: 20480
    NEXT_EXTENT:
    MIN_EXTENTS: 1
    MAX_EXTENTS: 2147483645
    PCT_INCREASE:
    FREELISTS:
    FREELIST_GROUPS:
    LOG: YES
    B: N
    NUM_ROWS:
    BLOCKS:
    EMPTY_BLOCKS:
    AVG_SPACE:
    CHAIN_CNT:
    AVG_ROW_LEN:
    AVG_SPACE_FREELIST_BLOCKS:
    NUM_FREELIST_BLOCKS:
    DEGREE: 1
    INSTANCES: 1
    CACHE: N
    TABLE_LO: ENABLED
    SAMPLE_SIZE:
    LAST_ANA:
    PAR: NO
    IOT_TYPE:
    ...

    2) SEGMENTS OF THE TABLE:

    SELECT *
    FROM DBA_SEGMENTS
    WHERE segment_name = 'X' and OWNER='Y'

    OWNER: Y
    SEGMENT_NAME: X
    PARTITION_NAME:
    SEGMENT_TYPE: TABLE
    TABLESPACE_NAME: Z
    HEADER_FILE:12
    HEADER_BLOCK:24819
    BYTES:65536
    BLOCKS:16
    EXTENTS:1
    INITIAL_EXTENT:20480
    NEXT_EXTENT:
    MIN_EXTENTS:1
    MAX_EXTENTS: 2147483645
    PCT_INCREASE:
    FREELISTS:
    FREELIST_GROUPS:
    RELATIVE_FNO: 12
    BUFFER_: DEFAULT

    3) FREE SPACE IN THE TABLESPACE:

    select max(bytes)/1048576 from dba_free_space
    where tablespace_name = 'Z';

    MAX(BYTES)/1048576
    ------------------
    68,25


    4) TABLESPACE INFORMATION :

    select TABLESPACE_NAME,MAX_EXTENTS
    from dba_tablespaces where tablespace_name='Z';

    TABLESPACE_NAME: Z
    BLOCK_SIZE: 4096
    INITIAL_EXTENT: 65536
    NEXT_EXTENT:
    MIN_EXTENTS: 1
    MAX_EXTENTS: 2147483645
    PCT_INCREASE:
    MIN_EXTLEN: 65536
    STATUS: ONLINE
    CONTENTS: PERMANENT
    LOGGING: LOGGING
    FOR: NO
    EXTENT_MAN: LOCAL
    ALLOCATIO: SYSTEM
    PLU: NO
    SEGMEN: AUTO


    5)DATAFILES OF THE TABLESPACE:

    FILE_NAME: C:\ORACLE\Z.ORA
    FILE_ID: 12
    TABLESPACE_NAME:Z
    BYTES: 314572800
    BLOCKS: 76800
    STATUS: AVAILABLE
    RELATIVE_FNO: 12
    AUT: NO
    MAXBYTES: 0
    MAXBLOCKS: 0
    INCREMENT_BY: 0
    USER_BYTES: 314507264
    USER_BLOCKS: 76784

    As you can see the NEXT_EXTENT parameter of the table's storage is not present. In DBA-STUDIO the value showed is 0.
    (I don't know the reason). But when I try to change this value to 10Mb (a multiple of the block_size) I get the error ORA-25150, telling me that the extent parameter value that I'm trying to use is not permitted. Also I have increased succesfully the size of the tablespace's datafile in 100 Mb. But I have the same bad results trying to modify the NEXT_EXTENT value...

    I have concluded that:

    * i) I don't know why the table cannot extent in the tablespace (ORA-01653 ).
    * ii) I don't know why I cannot change the NEXT_EXTENT parameter of the table's storage in 10 Mb (ORA-25150).
    * iii ) I don't know if I solve the point "ii)" will help me to solve the point "i)".

    Thanks a lot for all your ansers

    Regards

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    ii. You cannot specify NEXT_EXTENT because it's a LMT with auto allocation.

    i. Most probably the problem is because of ths not enough space in the tablespace for the next extent. Can you check how much free space left in the tablesapce. Check in DBA_FREE_SPACE.

    HTH
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    How can I calculate the next extent size of a data segment in a LMT-AUTO ALLOC?

    Thank you very much for your answer.

    I executed the query:

    (The results are in MB)

    select file_id, block_id, bytes/1024/1024 from dba_free_space
    where tablespace_name='Z'

    FILE_ID BLOCK_ID BYTES/1024/1024
    ---------- ---------- ---------------
    7 19887 ,01953125
    7 19027 ,13671875
    7 1307 3,046875
    7 20232 11,6992188
    7 23917 156,578125
    7 19187 ,76171875

    I have the doubt:

    How can I know that the free space of a auto allocated LMT is enought to support the next extent of a table's segment? How can I calculate the next extent size of a data segment in a LMT AUTO ALLOC?

    Regards, and thank you very much again.

  4. #4
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    Remember this point:

    For autoallocate in locally managed tablespace Oracle manages all extent allocation and sizing automatically, based on whatever value is specified for the initial extent. So the next extent is always the same as initial extent.

    Regarding your problem, to allocate the extent to an object, the extents should be adjacent. To acheive this, you can coalesce adjacent free space as follows:

    ALTER TABLESPACE Z COALESCE;
    Agasimani
    OCP(10g/9i/8i/8)

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Originally posted by agasimani
    Remember this point:

    For autoallocate in locally managed tablespace Oracle manages all extent allocation and sizing automatically, based on whatever value is specified for the initial extent. So the next extent is always the same as initial extent.
    This is not true if you are using AUTOALLOCATE. Oracle allocate extents of size based on grwoth of the table.

    Excerpts from Oracle Magazine:
    If you create a new table, the first extent will be 64KB in size. Subsequent extents will also be 64KB, until the table reaches 1MB in size. At that point the size of subsequent extents for the table will be increased to 1MB. When the table reaches 64MB in size, the extent size will be increased again to 8MB. Finally, if the table reaches a size of 1GB, the extent size will be increased one last time, to 64MB.
    http://www.oracle.com/oramag/oracle/...ml?o60o8i.html

    Turin,
    Are you sure about the results posted from DBA_SEGMENTS ? I see the table only one extent big.
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  6. #6
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Thanks all of you again.

    Yes, I'm pretty sure that I have only one segment of 64 Kb.
    The next extent's size (the 2nd) will have a size of 64 Kb according with the Oracle's storage formula for LMT AUTO ALLOC.
    My free space, according white dba_free_space view, is 156,578125 Mb.
    So, I think there aren't any reason for expecting lack of space in my LMT.



    Regards.
    Last edited by Turin; 02-04-2003 at 11:29 AM.

  7. #7
    Join Date
    Dec 2000
    Posts
    43
    [QUOTE]Originally posted by Turin
    [B]Thanks all of you again.

    Yes, I'm pretty sure that I have only one segment of 64 Kb.
    The next extent's size (the 2nd) will have a size of 64 Kb according with the Oracle's storage formula for LMT AUTO ALLOC.
    My free space, according white dba_free_space view, is 156,578125 Mb.
    So, I think there aren't any reason for expecting lack of space in my LMT.


    you may want to check how did you create your tablespace.
    I just got exactly the same mesage on testing system - 9.2 gave me the same message during creation of simple table with blob column on the tablspace created as:

    CREATE TABLESPACE CMM2_TSP_TBL03 LOGGING DATAFILE '/oracle_dbf1/oradata/ENKLAV/t
    bl3_1.dbf' SIZE 1999M REUSE AUTOEXTEND OFF EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

    Databases`s block size is 16K. It works on tablespace created as just 'MANAGEMENT LOCAL' and 'MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO'.

    I did not have time to check the reason of it yet, probably it`s supposed to be so...

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