DBA_FREE_SPACE and TEMP datafiles
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: DBA_FREE_SPACE and TEMP datafiles

  1. #1
    Join Date
    Dec 2000
    Posts
    43
    Ok here is the situation: Developer calls me stating that she is running a second attempt at running a Discoverer Report that will run 4 hours.

    The previous attempt to run the report failed 4 hours into it stating that it couldn't extend in the TEMP tablespace.

    The second time she ran the report I monitored DBA_FREE_SPACE, watching the space go down. When it got to 40Mb left I added 100Mb datafile to TEMP tablespace. I continued to watch it go down to .038Mb and the report finished. I was amazed, but suddenly started wondering if that could be accurate. How lucky would that have been to add exactly what I needed to finish the job!

    Ok, now the report ran without the error, and the free_space queries I do say that the TS has .038 free. The space has not come back to the view. Is that because it will be reused or what?

    Could anyone shed some light on if you can really tell how full your TEMP tablespace is by using the DBA_FREE_SPACE view?

    Thanks !


  2. #2
    Join Date
    Dec 2000
    Posts
    43
    I just refreshed my memory. Temp segments extend until they are no longer needed by the process then they are dropped. Shouldn't that drop happen as soon as the developer's script stops and he or she logs off?


  3. #3
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    In the temp tablespace, the temporary segment keeps growing till the database is shutdown. No deallocation is done till that time. Hence you might be seeing the result like that.

  4. #4
    Join Date
    Dec 2000
    Posts
    43
    Thanks. So after you fill up your TEMP datafiles from the time you last bounced your server IS there a way to find out how much real temp space you have left?

  5. #5
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    You can check it from the view V$SORT_USAGE to see how much space is being used by current sort operations.

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