TEMP, session and UNDO tablespace administration

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;