ORA-1653
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ORA-1653

  1. #1
    Join Date
    Jun 2001
    Posts
    76
    All -

    When trying to insert 500k records into a table which already holds 19.5M records, I get the following error.

    ORA-01653: unable to extend table BILL.MEASUREMENT_DATA by 1574802 in tablespace BILLING

    I have tried the following:

    1) Add datafile to tablespace billing
    2) Add rollback segments
    3) Coalescing ALL tablespaces
    4) Adding additional hard drive space (although there are ~100 GB free)

    None of these have worked.

    Any suggestions?

    Thanks,
    R
    On the other hand, you have different fingers.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Check in DBA_TABLES field maxextent for this table.
    execute -->
    alter table ... storage(maxextents unlimited);
    increase size of next (extent) for this table.

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    Making datafile grow to unlimited or adding another datafile should solve this problem . Make sure your tablespace level parameters . Make no of extents unlimited .
    Raghu

  4. #4
    Join Date
    Jun 2001
    Posts
    76
    I neglected to mention...

    I have changed all tablespaces to maxextents unlimited, autoextend on. However, I have not changed the tables. Let me try...

    I just set maxextents unlimited and no dice. However, i did notice the following from dba_tables

    TABLE_NAME NEXT_EXTENT MAX_EXTENTS
    ------------------------------ ----------- -----------
    MEASUREMENT_DATA 3225194496 2147483645

    that was AFTER i set maxextents unlimited and restart the db...


    R
    On the other hand, you have different fingers.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    ORA-01653 has nothing to do with maxextents setting (either on tablespace or on table). It simply means that Oracle tries to allocate another extents to insert new rows, but there is not enough room for this new extent in any of the tablespace's datafile.

    No wonder you get this error, the size of this new extent it tries to allocate is 3GB! So you'll have to add a new file of the size at lesat 3GB or enlarge one of the existing files by 3 GB.

    I don't suggest any of the above option. 3GB for a single extent is simply too much! You better change your next extent size for this table to something managable and set PCTINCREASE for this table to 0 (I bet now it is set to the default 50). So what you need to do is:

    ALTER TABLE my_big_table STORAGE (NEXT 128M PCTINCREASE 0);

    Yo can change 128M in the above example to whatever size you thing will serve you best.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2001
    Posts
    267
    Haa ..3gb .This must be a big dataware house project ..
    Raghu

  7. #7
    Join Date
    Jun 2001
    Posts
    76
    Thank You, it is fixed.

    Although, pctincrease was already set to 0. I did what you said and set next 128M. That fixed it. Although I am rather confused as to how the next extent got so large... How can something like this happen/what can i do to prevent this in the future?

    R
    On the other hand, you have different fingers.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Use:

    SELECT extent_id, bytes FROM user_extents
    WHERE segment_name = 'MY_BIG_TABLE'
    ORDER BY extent_id;

    You'll get the size of each extent as they were allocated by Oracle. I guess you'll find out that at the beginning each extent is bigger from the previous one by 50 % (or whatever PCTINCREASE was set to at that time). Now at some time, someone changed the PCTINCREASE to 0, but forgot to set NEXT to some managable size, so it remained at 3GB.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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