Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Posts
    68

    Question Unanswered: ORA-01630: max # extents Error

    All,

    I am getting this Oracle Error:

    ORA-01630: max # extents (249) reached in temp segment in tablespace LOCAL

    tried to use the code below to fix it:

    ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 1M NEXT 10M);

    But that didn't work either does anyone have an ideas?
    I'm getting 'alter' as a not found command

    Can anyone help me,
    John316

  2. #2
    Join Date
    Oct 2003
    Location
    Colorado
    Posts
    4
    You don't mention what release of Oracle you're running and it may affect some of the long-term options you have.

    The message that you posted suggests that the tablespace is named 'LOCAL'. Attempting to change the default storage in tablespace 'TEMP' isn't going to help 'LOCAL'.

    Try the same change against 'LOCAL' and see if that fixes the problem.

    However, if it does, there are still some additional concerns. First, you probably don't want temp segments being created in 'LOCAL'. If you have a temporary tablespace then each user should be altered to point their temporary tablespace to it:

    ALTER USER SCOTT TEMPORARY TABLESPACE TEMP;

    You should also alter the tablespace to be of type TEMPORARY so no other permanent objects get created there and to allow Oracle to better manage the extent allocation:

    ALTER TABLESPACE TEMP TEMPORARY;

    It would be good to have your tablespaces created with local extent management. Unfortunately this can't be done with an ALTER...you will have to create a new tablespace. As far as handling a temporary tablespace, I'd suggest creating a new one (such as TEMP2) with local management and then going through and altering all users to point them to TEMP2. When you're done (and all users' queries that are using TEMP are complete) then you can drop tablespace TEMP.

    Along the same lines, you might want to check the PGA_AGGREGATE_TARGET (if on 9i) or SORT_AREA_SIZE (on earlier releases) to see if you are using sort memory efficiently. STATSPACK (or BSTAT/ESTAT) will give you an idea of how many memory vs. disk sorts are being executed on the database

Posting Permissions

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