How to clean LMT TEMP tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to clean LMT TEMP tablespace

  1. #1
    Join Date
    Sep 2006
    Posts
    19

    How to clean LMT TEMP tablespace

    Hi all
    We are having oracle 9i and tablespaces are LMT
    Recently we got following err msg in alert.
    ORA-1652: unable to extend temp segment by 128 in tablespace
    TEMP

    I checked the following
    Qry->1
    -------
    SELECT tablespace_name, extent_size, total_extents, used_extents,
    free_extents, max_used_size FROM v$sort_segment;

    TABLESPACE_NAME EXTENT_SIZE TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS MAX_USED_SIZE
    ------------------------------ ----------- ------------- ------------ ------------ -------------
    TEMP 128 11140 0
    11140 0

    Qry->2
    -------
    SQL> select TABLESPACE_NAME,CONTENTS,INITIAL_EXTENT,NEXT_EXTENT,MAX_EXTENTS,PCT_INCREASE,EXTENT_MANAGEMENT,SEGME NT_SPACE_MANAGEMENT
    2 from dba_tablespaces where tablespace_name like '%TEMP%';

    TABLESPACE_NAME CONTENTS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE EXTENT_MAN SEGMEN
    ------------------------------ --------- -------------- ----------- ----------- ------------ ---------- ------
    TEMP TEMPORARY 1048576 1048576 0 LOCAL MANUAL

    QRY-3
    ------
    SQL> select tablespace_name, file_id, bytes_used, bytes_free
    2 from v$temp_space_header ;

    TABLESPACE_NAME FILE_ID BYTES_USED BYTES_FREE
    ------------------------------ ---------- ---------- - ---------
    TEMP 1 5242880000 0
    TEMP 2 3221225472 0
    TEMP 3 2147483648 0
    TEMP 4 1073741824 0

    can you tell why in v$sort_segment it's showing free and in $temp_space_header it is showing bytes used as 0 and how to clear/deallocate temp segments in temp space.WE ARE RESTARTING THE DATABASE DAILY IN THE MORNING.

    Regards

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you cant 'clear' it, if it needs more space it needs more space - why not increase it?

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    The best way is to find the session(s) that consumes excessive amount of temporary space.
    The query below might be useful.
    Code:
    select sid, serial#, sum(extents) as extents, tablespace, sql_text
    from v$session, v$sort_usage, v$sql
    where v$session.serial#=v$sort_usage.session_num
    and v$session.saddr=v$sort_usage.session_addr
    and v$sql.address(+)=v$session.sql_address
    group by sid, serial#, tablespace, sql_text
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you dont free spaces in temporary tablespaces, it just works tha way, trust what v$sort_segment says!

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