Error: ORA-1361: max # extents 1024 reached in table AA.BB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Error: ORA-1361: max # extents 1024 reached in table AA.BB

  1. #1
    Join Date
    Dec 2010
    Posts
    11

    Smile Error: ORA-1361: max # extents 1024 reached in table AA.BB

    Hi friends,
    Please anyone help me in solving the problem.
    i checked in the alert logs in Oracle Database and noticed an Error.
    The error is :
    ORA-1361: max # extents 1024 reached in table AA.BB

    please suggest how to solve the problem and
    Also tell me the reason why this error is coming?
    what will be the effect of this error on the database if i will not rectify this error in the database?

    I am new to the Oracle. So, please suggest me with deatils.

    thanks
    regards

    Jitender Singh

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    This error is coming because the table reached max extents. Increase the max extents to unlimited or any higher number which is higher than the current value using the below command.

    ALTER TABLE AA.BB STORAGE(MAXEXTENTS UNLIMITED);

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    If you dont want to have unlimited extents I see two more options available
    to you:

    1) You can export the data, drop the table and re-create with larger initial and next storage parameters, this will lower the number of extents.


    2) You can try de-allocating the unsed space beyond the High Water Mark (alter table table_name deallocate unused keep 0, as shown on the next demo:


    SQL> edit
    Wrote file afiedt.buf

    1 create table emp1
    2 storage( initial 8m next 8m )
    3* as select * from employees
    SQL> /

    Table created.

    SQL> analyze table emp1 compute statistics;

    Table analyzed.


    SQL>
    1 select blocks, segment_name from user_segments
    2* where segment_name ='EMP1'

    BLOCKS SEGMENT_NAME
    ---------- ----------------
    1024 EMP1


    SQL> alter table emp1 deallocate unused keep 0;

    Table altered.

    SQL> select blocks, segment_name from user_segments
    2 where segment_name ='EMP1';

    BLOCKS SEGMENT_NAME
    ---------- ----------------
    8 EMP1

    SQL> select table_name, initial_extent from user_tables;

    TABLE_NAME INITIAL_EXTENT
    ------------------------------ --------------
    EMP1 65536
    EMPLOYEES 65536

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by BeefStu View Post
    1) You can export the data, drop the table and re-create with larger initial and next storage parameters, this will lower the number of extents.
    Oh yes... this is provided you are manually handling extent sizes and your boss is concerned about running out of extents, huh?

    Quote Originally Posted by BeefStu View Post
    2) You can try de-allocating the unsed space beyond the High Water Mark
    If you already got ORA-1631 it means there is nothing beyond HWM
    Last edited by PAVB; 12-30-2010 at 09:52 AM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2010
    Posts
    12
    It will be nice when everyone starts using ASM and ASSM. Then these silly questions will finally just go away. Use Locally Managed Tablespaces, ASM and ASSM with storage devices (preferably a SAN implementation) commensurate with the application and you will get a lot more sleep.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by onedbguru View Post
    It will be nice when everyone starts using ASM and ASSM. Then these silly questions will finally just go away. Use Locally Managed Tablespaces, ASM and ASSM with storage devices (preferably a SAN implementation) commensurate with the application and you will get a lot more sleep.
    Well... in that line of thinking do you think the very same day databases gain conciousness all questions will finally go away? Don't think so, some people will still ask questions like "what it means don't touch anything? what's anything?"

    I have a list of things I never underestimate, in between them: human lazyness
    Last edited by PAVB; 12-30-2010 at 09:52 AM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2010
    Posts
    12
    It was once said that the mother of invention was necessity. I would say that the mother of invention is actually laziness - or is it that innate desire in all of us to sleep at night.

    When you deal with VLDB's (hundreds of TB) you will want to use all of the tools you can to keep the thing running. The side-effect is that you get to sleep at night...

    Can you detect a common theme here

    And if Larry has his way, the db becoming self-aware may not be that far off lol

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