40 lines
1.3 KiB
SQL
40 lines
1.3 KiB
SQL
-- -----------------------------------------------------------------------------------
|
|
-- File Name : https://oracle-base.com/dba/monitoring/top_sessions.sql
|
|
-- Author : Tim Hall
|
|
-- Description : Displays information on all database sessions ordered by executions.
|
|
-- Requirements : Access to the V$ views.
|
|
-- Call Syntax : @top_sessions.sql (reads, execs or cpu)
|
|
-- Last Modified: 21/02/2005
|
|
-- -----------------------------------------------------------------------------------
|
|
SET LINESIZE 500
|
|
SET PAGESIZE 1000
|
|
SET VERIFY OFF
|
|
|
|
COLUMN username FORMAT A15
|
|
COLUMN machine FORMAT A25
|
|
COLUMN logon_time FORMAT A20
|
|
|
|
SELECT NVL(a.username, '(oracle)') AS username,
|
|
a.osuser,
|
|
a.sid,
|
|
a.serial#,
|
|
c.value AS &1,
|
|
a.lockwait,
|
|
a.status,
|
|
a.module,
|
|
a.machine,
|
|
a.program,
|
|
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
|
|
FROM v$session a,
|
|
v$sesstat c,
|
|
v$statname d
|
|
WHERE a.sid = c.sid
|
|
AND c.statistic# = d.statistic#
|
|
AND d.name = DECODE(UPPER('&1'), 'READS', 'session logical reads',
|
|
'EXECS', 'execute count',
|
|
'CPU', 'CPU used by this session',
|
|
'CPU used by this session')
|
|
ORDER BY c.value DESC;
|
|
|
|
SET PAGESIZE 14
|