Oracle - how to check sessions, temp and other things

Post date: Mar 31, 2013 7:22:28 PM

How to list sessions to a database?

SELECT MACHINE, STATE, STATUS, USERNAME, SCHEMANAME, OSUSER, PROGRAM, LOGON_TIME from V$SESSION

How to monitor used temp tablespace?

SELECT TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

select TABLESPACE_NAME, BYTES_USED/(1024*1024*1024), (BYTES_FREE)/(1024*1024*1024) from V$TEMP_SPACE_HEADER;

Temporary segments

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;

Sort space use by session

SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,

S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

COUNT(*) sort_ops

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr

AND S.paddr = P.addr

AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

S.program, TBS.block_size, T.tablespace

ORDER BY sid_serial;

Sort space usage by statement

SELECT S.sid || ',' || S.serial# sid_serial, S.username,

T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,

T.sqladdr address, Q.hash_value, Q.sql_text

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

AND T.sqladdr = Q.address (+)

AND T.tablespace = TBS.tablespace_name

ORDER BY S.sid;

How to see what DB/Schema is doing?

SELECT S.USERNAME||'('||s.sid||')-'||s.osuser UNAM

,s.program||'-'||s.terminal||'('||s.machine||')' PROG

,s.sid||'/'||s.serial# sid

,s.status "Status",p.spid

,sql_text sqltext

from v$sqltext_with_newlines t,V$SESSION s , v$process p

where t.address =s.sql_address

and p.addr=s.paddr(+)

and t.hash_value = s.sql_hash_value

and s.machine like '%VMCAST-ANA-ST-4%'

order by s.sid,t.piece