Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    28

    Unanswered: Question about temp tablespace usage

    Greetings -

    I understand that it is "normal" that temp tablespaces appear full, as the segments are not de-allocated. How can I tell how much space is being used by active queries? IE: Is there any way to know if I'm about to run out of temp tablespace, or do I just have to wait until an "out of temp tablespace" appears in my server error logs and the users query fails?

    Thanks,

    Amy

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    v$sort_usage, v$sort_segment

    These were for 8i/9i - should be OK in 10:

    Code:
    select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS,
    round((used_blocks/total_blocks), 2) "PERC_USED"
    from v$sort_segment;
    or for user details:

    Code:
    SELECT
       a.sid, a.username, a.osuser, a.program, a.tablespace_name,
       a.bytes "BYTES_USED", round(a.blocks / b.total_blocks,3) "PERC_USED"
    from
       (select sor.tablespace_name, ses.sid, ses.username,
          ses.osuser, ses.program,
          sor.blocks, sor.bytes
       from
         (select /*+ optimizer rule */
            u.tablespace "TABLESPACE_NAME", u.session_addr,
            sum(u.blocks) blocks,
            sum(u.blocks) * &blksize bytes
          FROM v$sort_usage u
            GROUP BY u.session_addr, u.tablespace) sor,
          (select /*+ optimizer rule */
            saddr, sid, username, osuser, program
            from v$session) ses
       where ses.saddr=sor.session_addr) a,
       (select tablespace_name, sum(decode(maxblocks,0,blocks,maxblocks)) "TOTAL_BLOCKS"
       from dba_temp_files group by tablespace_name) b
       where a.tablespace_name=b.tablespace_name
    order by a.bytes

  3. #3
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    Follow up -

    I always add a job in OEM to watch v$sort_segment for a certain % full, and email me when it happens - so I can catch who is doing it...

Posting Permissions

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