Oracle Database Query
Logons Cumulative
SELECT 
 sys.database_name||':'||
 '  Current logons='|| TO_NUMBER(l.sessions_current)||
 '  Cumulative logons='|| SUBSTR(ss.value,1,10)||
 '  Highwater mark='||l.sessions_highwater  DB_logon_Cumulative_details
FROM
   v$sysstat ss,  v$license l
WHERE ss.statistic# = 0       -- ss.name = 'logons cumulative' 
;
Opened Cursors Current
-- Summary: Opened Cursors Current
SELECT
 SUBSTR(s.username,1,25) username, s.machine, SUM(ss.value) total_cur,
 ROUND(AVG(ss.value),4) avg_cur, MAX(ss.value) max_cur
FROM   v$sesstat ss, v$statname sn, v$session s 
WHERE  ss.statistic# = sn.statistic# 
AND    s.sid = ss.sid
--  AND    sn.name = 'opened cursors current'
AND    sn.statistic#  = 3
GROUP BY  SUBSTR(s.username,1,25), s.machine
ORDER BY 
 total_cur DESC
;

-- Detail: Opened Cursors Current
SELECT
 s.osuser, s.username, TO_CHAR(s.LOGON_TIME,'mm/dd/yyyy hh24:mi:ss') log_on, 
 ss.value, s.sid, s.serial# serial_no 
FROM  v$sesstat ss, v$statname sn, v$session s
WHERE a.statistic# = sn.statistic#  
AND   s.sid = ss.sid
AND   sn.statistic#  = 3;

-- Note: List of all statistic number and names
SELECT stat_id, statistic#, name, class FROM v$statname;

General Database Information

NLS and DB Properties

Host, instance and others


Oracle registered trademark of Oracle Corporation.

Last Revised on: March 01th, 2014

  397