How I can free the temporary tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How I can free the temporary tablespace

Hybrid View

  1. #1
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110
    HI,

    Is there any way to 'free' the temporary tablespace allocated segments other than shut down instance.Will the coalascing command will work for temporary tablespace.

    Any advise ...

    Manoj

  2. #2
    Join Date
    Sep 2002
    Posts
    3
    Hi,

    I don't think you can really deallocate or coalesce space fom the temporary tablespace of type 'TEMPORARY'.

    The temporary (sort) segment of a given temporary tablespace is created at the time of the first sort operation, which has to write to disk to free up sort space in memory. The first disk sort creates a sort segment in the temporary tablespace. Multiple transactions which need asort on disk, can share the same sort segment, however, they cannot share the same extent. The sort segment expands by allocating new extents. The sort extents are not de-allocated while the instance is running, but are marked as free and can be re-used as required.

    Thanks

  3. #3
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110
    Hi,

    Thanks for the reply..

    How can I find whether it is marked as free ?

    Manoj

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Select from sys.fet$ to see the freed blocks.

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    v$sort_segment

  6. #6
    Join Date
    Jul 2002
    Posts
    205
    The following Queries may help you, how much TEM tablespace is used and how much free.

    --SORT AREA SIZE

    SELECT tablespace_name, extent_size, total_extents, used_extents,
    free_extents, max_used_size
    FROM v$sort_segment;

    SELECT s.username, u.tablespace, u.contents, u.extents, u.blocks
    FROM v$session s, v$sort_usage u
    WHERE s.saddr=u.session_addr;

    select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS
    from v$sort_segment;


  7. #7
    Join Date
    Jul 2002
    Location
    Washington DC
    Posts
    110
    Hi,

    Thanks a lot for the inputs...

    Let me work out..

    Manoj

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