Ora Processes
main monitor ddl objects dml stato e configurazione rman & dpump pl/sql cluster ASM
main config start-shut process memory monitor diag
user rman & dpump pl/sql cluster ASM

List Sessions many parameters

set echo off
set time on
set timing on
set pages 200
set lines 200
set trims on
set wrap on
col sid            for 99999
col osuser         for a10
col username       for a14
col machine        for a10
col client_info    for a20
select TO_CHAR(v$s.logon_Time,'DD-MON-YYYY HH24:MI:SS') as logon_time,
       v$s.sid,
       v$s.serial#,
       v$s.osuser,
       v$s.username,
       v$p.spid,
       v$s.status,
       v$s.machine,
       v$s.program,
       v$s.event,
                   v$s.sql_id
  from v$session v$s, V$process v$p
where v$s.paddr=v$p.addr
   --and v$s.username like '%&utente%'
   --and v$s.sid= '&sid'
   --and v$p.spid IN ('','')
   --and v$s.machine LIKE '%&machine%'
   --and v$s.program LIKE '%rman%'
   --and v$s.status='ACTIVE'
   --and v$s.event like '%enq%'
   --and trunc(logon_time)<trunc(sysdate-1)
   --and v$s.service_name like '%&service%'
order by 1
/

List sessions with SQL TEXT

col username for a24
col sql_text for a64
set pages 5000 line 200
SELECT sess.sid, sess.username, sess.program,sess.command, sess.logon_time, sess.lockwait, sql_text
FROM v$sqlarea sqlarea, v$session sess
WHERE sess.sql_hash_value = sqlarea.hash_value
AND sess.sql_address = sqlarea.address
-- AND sess.sid = &sesid
-- AND sess.status = 'ACTIVE'
-- AND sess.username is not null
;

List sessions MEMORY usage

set line 200 pages 5000
col program for a32
col logon_time for a16
select m.username, m.sid, TO_CHAR(s.logon_time, 'DD-MM HH24:MI:SS') logon_time, s.command, s.program, s.status, s.lockwait, m.sess_MB 
    from (SELECT   NVL (username, 'SYS-BKGD') username, sess.SID, ROUND(SUM(VALUE)/1024/1024,1) sess_MB
        FROM v$session sess, v$sesstat stat, v$statname NAME
        WHERE sess.SID = stat.SID
            AND stat.statistic# = NAME.statistic#
            AND NAME.NAME LIKE 'session % memory'
        GROUP BY username, sess.SID
        order by 3 desc) m, v$session s
   where m.sid = s.sid
   and sess_MB > &minsize;

List Events with WAITS

SET line 200 pages 5000
col event for a40
SELECT event, WAIT_CLASS# w_class#, wait_class, total_waits tot_waits, total_timeouts tot_timeouts, (time_waited / 100) tot_time, (average_wait / 100) avg_time 
FROM v$system_event
WHERE wait_class# <> 6
ORDER by total_waits DESC 
/

List sessions with STATUS and SID,SERIAL#

set line 200 pages 5000
SELECT sid,serial#,status.lockwait

Spool of ddl 'kill INACTIVE sessions'

set pages 50000
spool kill_inactives.sql
select trim('alter system kill session '''|| sid || ', ' || serial# || ''' immediate;') killcmd from v$session where status='INACTIVE';   
spool off

CPU usage

SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000
 
SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License