Active Sessions, Proxy Sessions and Locked objects in Oracle

I have received few requests from Oracle User community to provide SQL queries related to active sessions, proxy sessions and locked objects in a session. Here are few SQL queries which you can use in day to day monitoring.

 
--------------------------------------------------------
--Script : ACTIVE SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600

COLUMN username FORMAT A10
COLUMN osuser FORMAT A10

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN status FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A40
COLUMN module FORMAT A35
COLUMN action FORMAT A15
COLUMN logon_time FORMAT A20

SELECT s.username,

s.osuser,

s.sid,

s.serial#,

p.spid,

s.lockwait,

s.status,

s.machine,

s.program,

s.module,

s.action,

TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,

s.blocking_session_status AS BlockStatus

FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

------------------------------------

--Script : Active Session waits

--Author : oracleracexpert.com

------------------------------------

SET PAGESIZE 1000

SET LINESIZE 600

COLUMN username FORMAT A10

COLUMN osuser FORMAT A10

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN spid FORMAT A10

COLUMN state FORMAT A10

COLUMN wait_class FORMAT A20

COLUMN seconds_in_wait FORMAT 999999999

COLUMN module FORMAT A35

COLUMN blocking_session FORMAT A20

COLUMN blocking_session_status FORMAT A20

SELECT s.username,

       s.osuser,

       s.sid,

       s.serial#,

   p.spid, 

       s.state,

   s.wait_class,

       s.seconds_in_wait,

       s.module,

       TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,

   s.blocking_session,

       s.blocking_session_status AS BlockStatus

FROM   v$session s, v$process p

WHERE  s.paddr  = p.addr

AND    s.status = 'ACTIVE'

ORDER BY 1,2;

--------------------------------------------------------
--Script : PROXY SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600

COLUMN username FORMAT A10
COLUMN osuser FORMAT A10

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN status FORMAT A10
COLUMN machine FORMAT A20
COLUMN program FORMAT A40
COLUMN module FORMAT A35
COLUMN action FORMAT A15
COLUMN logon_time FORMAT A20

SELECT s.username,

s.osuser,

s.sid,

s.serial#,

p.spid,

s.lockwait,

s.status,

s.machine,

s.program,

s.module,

s.action,

TO_CHAR(s.logon_Time,'MM-DD-YYYY HH24:MI:SS') AS logon_time,

s.blocking_session_status AS BlockStatus

FROM  v$session s, v$process p, v$session_connect_info sci
WHERE s.paddr = p.addr
AND s.sid = sci.sid
AND s.serial# = sci.serial#
AND sci.authentication_type = 'PROXY'
ORDER BY s.username, s.osuser;

--------------------------------------------------------
--Script : LOCKED OBJECT SESSIONS
--Author : oracleracexpert.com
--------------------------------------------------------
SET PAGESIZE 1000
SET LINESIZE 600

COLUMN sid FORMAT 9999

COLUMN serial# FORMAT 999999999

COLUMN status FORMAT A10
COLUMN owner FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_type FORMAT A15
COLUMN oracle_username FORMAT A15
COLUMN locked_mode FORMAT A15
COLUMN os_user_name FORMAT A15

SELECT s.sid,

s.serial#,

s.status,

do.owner,

do.object_name,

do.object_type,

lo.oracle_username,

Decode(lo.locked_mode, 0, 'None',

1, 'Null (NULL)',

2, 'Row-S (SS)',

3, 'Row-X (SX)',

4, 'Share (S)',

5, 'S/Row-X (SSX)',

6, 'Exclusive (X)',

lo.locked_mode) locked_mode,

lo.os_user_name

FROM v$locked_object lo, dba_objects do, v$session s
WHERE lo.session_id = s.sid
AND do.object_id = lo.object_id
ORDER BY 1, 2, 3, 4;

Thanks & Regards,

https://oracleracexpert.com