ora_01631max # of extents nnn reached in table table-name
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ora_01631max # of extents nnn reached in table table-name

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    ora_01631max # of extents nnn reached in table table-name

    Hi all
    One of our user had the following error loading data in a table in his own schema.

    ORA-01631: max # of extents nnn reached in table

    We are running Oracle 8.1.7.4 on Windows advanced server with db_block_size of 8192.


    According to the documentation the ideal solution is to Increase the maximum number of extents allowed for the table, i.e:

    For a 8K block size an object could have no more than 505 extents

    alter table storage (maxextents 500);

    When I query the database as follows

    SELECT max_extents , tablespace_name
    FROM dba_tables
    WHERE table_name = 'USERS' AND owner = 'ower_name';

    MAX_EXTENTS TABLESPACE_NAME
    2147483645 USERS

    Why I am getting 2147483645 for max_extents instead of 505 FOR 8K block size

    Can some one explain that?
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Apr 2003
    Posts
    353
    select next_extent from dba_tables where table_name=;

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: ora_01631max # of extents nnn reached in table table-name

    Originally posted by Ablakios
    According to the documentation .....
    For a 8K block size an object could have no more than 505 extents
    Which documentation are you reading? Those limits of maxextents based on db block size are ancient history, from times of earlier releases of Oracle7!

    The upper limit for maxextents is UNLIMITED, and this "unlimited value" in the database is represented by that large number which you are getting (2147483645).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372
    Thanks for the update Jmodic
    Do you think
    alter table table_name storage (maxextents unlimited); will do the trick
    or
    alter table table_name storage (maxextents integer_closed to_ unlimited);

    is there any repercusion for specifying UNLIMITED instead of a value closed to 2147483645?
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by Ablakios

    alter table table_name storage (maxextents unlimited); will do the trick
    Will do
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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