how to clean up TEMP tablespace ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: how to clean up TEMP tablespace ?

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    284

    how to clean up TEMP tablespace ?

    Hello all

    Oracle9.2 on sun 5.8

    My TEMP tbs is 98% FULL. This tbs is LOCALLY managed. How do we celan it up without bouncing the instance ?...


    Any help is appreciated.

    thanks
    Ron

  2. #2
    Join Date
    Sep 2002
    Posts
    411
    you can't clean up the TEMP tablespace in local extent in 9i. It shows 98% full b/c Oracle preallocated the tablespaces. you should not worry about it unless you want to drop the temp and recreate the new one.

  3. #3
    Join Date
    Oct 2002
    Posts
    284
    ____________________________________________________________
    It shows 98% full b/c Oracle preallocated the tablespaces.
    ___________________________________________________


    Thanks Mike2000...


    I ddn't get it when u said "preallocated ". Do u mean that Oracle 9i has preallocated 'x' bytes of space for this TBS ?...

    reason is , my other 9i DB TEMP tbs is only 17% Full..


    any help is appreciated...

    thanks
    ron

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    What you need to do is read the Oracle documentation on how it uses temporary tablespaces -- it changed in 8i.

    Search the forum for previous questions on temp tablespaces being full, 'cos this gets asked about once a month.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Feb 2003
    Location
    INDIA
    Posts
    96
    Temporary segments cleared when Database is shutdown.

    A temporary segment created in temp tablespace will not be deallocated at the end of the sort. The extents are MARKED as free, are not placed back on the freelist, and are retained for reuse. An element called the Sort Extent Pool (SEP) is created in the SGA to describe them.
    Subsequent sort operations will then be allocated extents from this pool via a memory lookup.

    Dilip
    Dilip Patel
    OCP 8i

    Catch me online at Yahoo: ddpatel256

  6. #6
    Join Date
    Nov 2001
    Posts
    335
    Do you get an error trying to allocate big sort segment?
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  7. #7
    Join Date
    Aug 2001
    Location
    Indonesia
    Posts
    5
    Hi...

    I got the same problem, I can release some space in TEMP after running this query :
    SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks

    Then kill the session which is holding the segments, if you have TOP Session utility from OEM it will be easier...

    If you restart the DB will solve the problem...

    TIA
    Jeram
    Oracle DBA

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If you restart the DB will solve the problem...
    Which problem is that?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Originally posted by vr76413
    ____________________________________________________________
    It shows 98% full b/c Oracle preallocated the tablespaces.
    ___________________________________________________


    Thanks Mike2000...


    I ddn't get it when u said "preallocated ". Do u mean that Oracle 9i has preallocated 'x' bytes of space for this TBS ?...

    reason is , my other 9i DB TEMP tbs is only 17% Full..


    any help is appreciated...

    thanks
    ron
    Are you sure that in your other DB the TEMP tablespace is a temporary tablespace and not permanent tablespace.

    Do a select tablespace_name,file_name from dba_temp_files to find out if its actually a temporary tablespace.

    Because if its a LMT Temporary tablespace then the extents are
    pre-allocated.

    Code:
    sys@ACME.WORLD> create temporary tablespace TEMP_TBS tempfile 'e:/temp_tbs01.dbf' size 50M;
    
    Tablespace created.
    
    sys@ACME.WORLD> select tablespace_name,file_id,block_id,blocks,bytes/1024/1024 from v$temp_extent_map
      2  where tablespace_name = 'TEMP_TBS';
    
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID     BLOCKS BYTES/1024/1024
    ------------------------------ ---------- ---------- ---------- ---------------
    TEMP_TBS                                2          9        128               1
    TEMP_TBS                                2        137        128               1
    TEMP_TBS                                2        265        128               1
    TEMP_TBS                                2        393        128               1
    TEMP_TBS                                2        521        128               1
    TEMP_TBS                                2        649        128               1
    TEMP_TBS                                2        777        128               1
    TEMP_TBS                                2        905        128               1
    TEMP_TBS                                2       1033        128               1
    TEMP_TBS                                2       1161        128               1
    TEMP_TBS                                2       1289        128               1
    TEMP_TBS                                2       1417        128               1
    TEMP_TBS                                2       1545        128               1
    TEMP_TBS                                2       1673        128               1
    TEMP_TBS                                2       1801        128               1
    TEMP_TBS                                2       1929        128               1
    TEMP_TBS                                2       2057        128               1
    TEMP_TBS                                2       2185        128               1
    TEMP_TBS                                2       2313        128               1
    TEMP_TBS                                2       2441        128               1
    TEMP_TBS                                2       2569        128               1
    TEMP_TBS                                2       2697        128               1
    TEMP_TBS                                2       2825        128               1
    TEMP_TBS                                2       2953        128               1
    TEMP_TBS                                2       3081        128               1
    TEMP_TBS                                2       3209        128               1
    TEMP_TBS                                2       3337        128               1
    TEMP_TBS                                2       3465        128               1
    TEMP_TBS                                2       3593        128               1
    TEMP_TBS                                2       3721        128               1
    TEMP_TBS                                2       3849        128               1
    TEMP_TBS                                2       3977        128               1
    TEMP_TBS                                2       4105        128               1
    TEMP_TBS                                2       4233        128               1
    TEMP_TBS                                2       4361        128               1
    TEMP_TBS                                2       4489        128               1
    TEMP_TBS                                2       4617        128               1
    TEMP_TBS                                2       4745        128               1
    TEMP_TBS                                2       4873        128               1
    TEMP_TBS                                2       5001        128               1
    TEMP_TBS                                2       5129        128               1
    TEMP_TBS                                2       5257        128               1
    TEMP_TBS                                2       5385        128               1
    TEMP_TBS                                2       5513        128               1
    TEMP_TBS                                2       5641        128               1
    TEMP_TBS                                2       5769        128               1
    TEMP_TBS                                2       5897        128               1
    TEMP_TBS                                2       6025        128               1
    TEMP_TBS                                2       6153        128               1
    
    49 rows selected.
    
    sys@ACME.WORLD>
    See above, here i just created a temporary tablespace of size 50M and all extents were preallocated.

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I'll give you guys a little tip also regarding Oracle 9i's TEMP tablespace DATATFILES that caught me a while ago.

    Just say you have a 10 GB filesystem on UNIX, and we've decide to place a say... 2 4GB datafiles an a 2 GB Temp datafile under it.

    If you check the size of the files system... you'll see that there is still 2GB left on the filesystem (even though you've allocated the full 10GB). This is because even though you specified the TEMP TABLESPACE 2GB it's not utilised on unix until the TEMP tablespace is written to. Therefore, a while down the track, somebody comes along and notices 1.5 GB of free filesystem space under the file system and creates another 1.5GB datafiles... guess what, when you're TEMP tablespace starts being used again... it runs out of space.

    Just thought I'd mention it...

    Bye...
    OCP 8i, 9i DBA
    Brisbane Australia

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