Oracle session

As a Database Administrator (DBA), one of the most common troubleshooting activities requested is to identify and analyze the running processes on a database. This typically involves checking the status of sessions to determine whether they are active or inactive.

To perform this task, a DBA would typically use monitoring tools or query the system catalog views to retrieve information about active sessions and the associated processes. They would then analyze the data to identify any issues or performance bottlenecks, such as long-running queries or blocked processes. In addition to monitoring active sessions, a DBA may also need to identify inactive sessions or processes that are consuming resources without performing any useful work. This could be a sign of a poorly designed application or a resource-intensive query that is consuming excessive CPU or memory.

Overall, the goal of this activity is to ensure that the database is performing optimally and that resources are being used efficiently. By identifying and addressing any issues with running processes, a DBA can help to improve the overall performance and stability of the database.

set lines 200 pages 9999 tab off

col program for a25 heading 'Program' trunc

col LOGON-TIME for a17 heading 'Logon-Time'

col machine for a15 heading 'Machine/|Server Name'

col username for a30 heading 'User Name'

col osuser for a15 heading 'OS user'

col ses_info for a12 heading 'SID,SER#|SesInfo'

col process  for a10 heading 'Server|Proc ID'

col sql_id for a15 heading 'SQL ID'

col last_call_et for a16 heading 'Last UserCall|Act/Inact| DD HH24:MI:SS'

col status for a10  heading 'Session|Status'

SELECT

    c.inst_id,

    substr(c.sid

           || ','

           || c.serial#, 1, 12) ses_info,

    substr(c.osuser,1,15) OSUSER,

    substr(c.username, 1, 30) username,

    substr(c.program, 1, 30) "PROGRAM",

    nvl2(c.sql_id, 'C:' || c.sql_id, 'p:'

          || nvl(c.prev_sql_id, '/c --n/a--')) sql_id,

    substr(d.spid, 1, 10) "process",

    substr(c.machine, 1, 15) "MACHINE",

    c.status,

    to_char(logon_time, 'dd-mm-yy hh24:mi:ss') "LOGON-TIME",

       trim(to_char(trunc(c.last_call_et / 3600 / 24),'09'))||' '

    || trim(to_char(trunc(mod(c.last_call_et / 3600, 24)),'09'))||':'

    || trim(to_char(trunc(mod(c.last_call_et, 3600) / 60),'09'))||':'

    || trim(to_char(mod(mod(c.last_call_et, 3600), 60),'09'))  last_call_et

FROM

    gv$session   c,

    gv$process   d

WHERE

    c.paddr = d.addr

and c.inst_id=d.inst_id

ORDER BY

    c.inst_id,

    c.logon_time,

    c.program;

clear col

set tab on