Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143

    Unanswered: unable to extend temp segment by 128 in tablespace TEMP

    Hi all,

    We are currently experiencing problems with our TEMP tablespace and get the following message in the alert log occasionally:

    ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

    I've tried increasing the size of TEMP but it is not having much impact and therefore I wondered about pga_aggregate_target. I have looked in v$pgastat and it looks as though we are at 100% for sorts in memory so I think it is sized ok, but I'm not sure what else to do. Is there areything else I can look at to determine the problem?

    Thanks,
    Paula

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    This error does not necessarily indicate whether or not you have enough space in the tablespace, it merely indicates that Oracle could not find a large enough area of free contiguous space in which to fit the next extent.

    Sometimes you can resolve by:

    - Manually coalesce adjacent free extents:
    ALTER TABLESPACE <tablespace name> COALESCE;
    The extents must be adjacent to each other for this to work.

    - Add a datafile:
    ALTER TABLESPACE <tablespace name> ADD DATAFILE '<full path and file
    name>' SIZE <integer> <k|m>;

    - Resize the datafile:
    ALTER DATABASE DATAFILE '<full path and file name>' RESIZE <integer> <k|m>;

    - Enable autoextend:
    ALTER DATABASE DATAFILE ?<full path and file name>? AUTOEXTEND ON MAXSIZE UNLIMITED;

    - Defragment the Tablespace

    - Lower "next_extent" and/or "pct_increase" size:
    ALTER <segment_type> <segment_name> STORAGE ( next <integer> <k|m> pctincrease <integer>);

    And in some RARE occasions when you have a very complex join in which the sum of all returned column sizes exceeds the NEXT EXTENT (128 in your case), you may need to increase this NEXT EXTENT parameter.

    Good luck!

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Thank you for this. Very clear and concise!

  4. #4
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Is it possible to coalesce space in a locally managed TEMP tablespace?

    Also, the pctincrease=0, and the extents are uniform, so would coalescing make any difference?

    Many thanks,

    Paula.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Well TEMP is different to other tablespaces in that the extents are reused so temp is usually full (even if nothing is using the extents). So coalescing is usually a waste of time as there are no free extents.

    The reason for the error is someone is doing a large sort operation and this may for a short time stop other people doing large sorts. To fix this either create a larger temp tablespace or if there is another large sort op going on wait for it to finish and then re-try.

    v$temp_extent_pool will tell you how much of temp is currently being used by active transactions.

    Alan

  6. #6
    Join Date
    Aug 2004
    Location
    Manchester, UK
    Posts
    143
    Ok, thanks for this, I think I'll increase the size again.

    Paula

Posting Permissions

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