Posted in Database, SQL

Where is the TEMP space used?

The following statements should give us more indication about the TEMP usage and what is using it:

col username format a10
col osuser format a8
col SID_SERIAL format a8
col PROCESS format 9999999
col tablespace format a7
col status format a9
col size_mb format 9999999999999
set lin 300

SELECT   b.TABLESPACE
       --, b.segfile#
       --, b.segblk
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb 
       , a.SID
       , a.serial#
       , a.username
       , a.sql_id
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     AND a.status='ACTIVE'
     AND to_number(ROUND(((b.blocks * 8192)/1024/1024),2))>100
ORDER BY size_mb
       , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;
       
col username format a10
col osuser format a8
col SID_SERIAL format a8
col PROCESS format 9999999
col tablespace format a5
col status format a9
col space format 9999999999999
set lin 300
SELECT a.username, a.osuser, a.sid||','||a.serial# SID_SERIAL, c.spid Process,b.tablespace tablespace, a.status, sum(b.extents)* 1024*1024 space
FROM     v$session a,v$sort_usage b, v$process c, dba_tablespaces d
WHERE    a.saddr = b.session_addr
AND      a.paddr = c.addr
AND      b.tablespace=d.tablespace_name
AND      d.tablespace_name='&temp_tbs'
AND     a.status='ACTIVE'
group by a.username, a.osuser, a.sid||','||a.serial#, c.spid,b.tablespace, a.status
order by space;
Advertisements

Author:

I am an Oracle DBA with experience in Maximum Availability technology such as RAC, Data Guard, Grid Control and ASM. I worked with version 9i through 12c on a number of operating systems. Next to that I have an extensive experience with PL/SQL, Oracle eBusiness Suite and shell scripting. Specialties: Oracle RAC & ASM (Configuration/Monitoring/Tuning) Oracle Data Guard (Configuration/Monitoring/Tuning) EM12c (Installation / troubleshooting) SQL & PLSQL *nix Shell scripting

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.