TEMP Tablespace administration SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name; Free space in a temporary tablespace. select * from (select a.tablespace_name, sum(a.bytes/1024/1024) allocated_mb from dba_temp_files a where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x, (select sum(b.bytes_used/1024/1024) used_mb, sum(b.bytes_free/1024/1024) free_mb from v$temp_space_header b where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name); find out which SQL statement is using up space in a sort segment. select s.sid || ',' || s.serial# sid_serial, s.username, o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace, o.sqladdr address, h.hash_value, h.sql_text from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t where o.session_addr = s.saddr and o.sqladdr = h.address (+) and o.tablespace = t.tablespace_name order by s.sid; select file_name,sum(bytes)/1024/1024 from dba_temp_files where tablespace_name='TEMP' group by file_name; select file_name, sum(bytes/1024/1024/1024),tablespace_name from dba_temp_files where tablespace_name='TEMP'group by file_name, tablespace_name ; UNDO tablespace set lines 150 select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START", to_char(end_time,'hh24:mi dd-mon-yyyy') "END", unxpstealcnt, expstealcnt, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat order by end_time; -------------------------------------------- set lines 150 select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START", to_char(end_time,'hh24:mi dd-mon-yyyy') "END", undoblks, expblkreucnt, ssolderrcnt, nospaceerrcnt, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat order by end_time; Show all connected users set lines 100 pages 999 col ID format a15 select username , sid , serial# , status, last_call_et "Last Activity" from v$session where username is not null order by status desc, last_call_et desc 2. Time since last user activity set lines 100 pages 999 select username, floor(last_call_et / 60) "Minutes", status from v$session where username is not null order by last_call_et 3. Sessions sorted by logon time set lines 100 pages 999 set linesize 200 col ID format a15col osuser format a15 col login_time format a14 select username, osuser, sid , serial# , status, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time, last_call_et from v$session where username is not null order by login_time 4. Show user info including os pid col "SID/SERIAL" format a10 col username format a15col osuser format a15col program format a40 select s.sid , s.serial# , s.username, s.osuser, p.spid "OS PID", s.program from v$session s, v$process p Where s.paddr = p.addr order by to_number(p.spid) 5. Show a users current sql Select sql_text from v$sqlarea where (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like '&username') 6. Session status associated with the specified os process id select s.username, s.sid, s.serial#, p.spid, last_call_et, status from V$SESSION s, V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid' 8. Display any long operations set lines 100 pages 999 col username format a15 col message format a40 col remaining format 9999 select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops where time_remaining = 0 order by time_remaining desc 9. List open cursors per user set pages 999 select sess.username, sess.sid, sess.serial#, stat.value cursors from v$sesstat stat, v$statname sn, v$session sess where sess.username is not null and sess.sid = stat.sid and stat.statistic# = sn.statistic# and sn.name = 'opened cursors current' order by value Number of connection for the particular user from host column username format a20 column machine format a30 column session_count format 9,999 set lines 100 set pages 100 select username, machine, count(*) session_count from v$session where username = 'username' and upper(machine) like 'hostname%' group by username, machine order by username, machine; sort operation based on snap id set lines 200 col MODULE for a28 col SQL_TEXT for a80 wrap select snap_id,DBA_HIST_SQLTEXT.SQL_ID,MODULE,SORTS_TOTAL, SORTS_DELTA,SQL_TEXT from DBA_HIST_SQLSTAT,DBA_HIST_SQLTEXT where snap_id between 27381 and 27382 and DBA_HIST_SQLSTAT.SQL_ID=DBA_HIST_SQLTEXT.SQL_ID order by 4; Temp Segment: Track Temp Segment Free space: SELECT tablespace_name, SUM(bytes_used/1024/1024) USED, SUM(bytes_free/1024/1024) FREE FROM V$temp_space_header GROUP BY tablespace_name; SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used, D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free FROM v$sort_segment A, (SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total FROM v$tablespace B, v$tempfile C WHERE B.ts#= C.ts# GROUP BY B.name, C.block_size ) D WHERE A.tablespace_name = D.name GROUP by A.tablespace_name, D.mb_total; Track Who is Currently using the Temp: SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE", a.sid||','||a.serial# SID_SERIAL, a.username, a.program FROM sys.v_$session a, sys.v_$sort_usage b, sys.v_$parameter p WHERE p.name = 'db_block_size' AND a.saddr = b.session_addr ORDER BY b.tablespace, b.blocks; Undo & Rollback Segment: Monitor UNDO information: select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss'), maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat order by undoblks; Track Active Rollback Segment: SELECT r.NAME, l.sid, p.spid, NVL (p.username, 'no transaction') "Transaction", p.terminal "Terminal" FROM v$lock l, v$process p, v$rollname r WHERE l.sid = p.pid(+) AND TRUNC (l.id1(+) / 65536) = r.usn AND l.TYPE(+) = 'TX' AND l.lmode(+) = 6 ORDER BY R.NAME; Track Currently Who is using UNDO and TEMP: SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; ref: https://paulstuartoracle.wordpress.com/2014/07/20/analysing-temp-space-usage-with-ash-data/ column sum_max_mb format 999,999,999; column temporary_tablespace format A20 WITH pivot1 AS ( SELECT trunc(ash.sample_time,'MI') sample_time, ash.SESSION_ID, ash.SESSION_SERIAL#, ash.SQL_ID, ash.sql_exec_id, U.temporary_tablespace, max(temp_space_allocated)/(1024*1024) max_temp_mb FROM GV$ACTIVE_SESSION_HISTORY ash, dba_users U WHERE ash.user_id = U.user_id and ash.session_type = 'FOREGROUND' and ash.temp_space_allocated > 0 GROUP BY trunc(ash.sample_time,'MI'), ash.SESSION_ID, ash.SESSION_SERIAL#, ash.SQL_ID, ash.sql_exec_id, U.temporary_tablespace ) SELECT temporary_tablespace, sample_time, sum(max_temp_mb) sum_max_mb from pivot1 GROUP BY sample_time, temporary_tablespace ORDER BY temporary_tablespace, sample_time;