V$ view showing objects accessed by a session 2

[dbalearner]

Hi,

Is there is a dynamic view that shows what segments/objects such as table, index etc are being accessed by a session?

Thanks

 

[SantaMufasa]

No, but there are views that show what objects a session has LOCKED. You can also use Oracle's AUDIT feature to document who accesses what object, using what privilege.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dbalearner]

Thanks Santa.

I think V$ACCESS can be used. It says"locked" but I think it really means "you are accessing them".

For modules like triggers and procs I think I can use V$SESSION.MODULE column to see which session accessing these modules. Is that correct?

Learner

 

[dbalearner]

Just tried it in a busy system. V$ACCESS seems to have all

1* select DISTINCT TYPE from V$ACCESS
/
TYPE
------------------------
CURSOR
NON-EXISTENT
PACKAGE
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
TYPE
VIEW

 

[SantaMufasa]

dbalearner, I'd say that you've graduated from learner to instructor. I had never used v$access before...verrrrry interesting! Hava

for introducing me to something new.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[dbalearner]

Thanks Santa, you are very kind.

Hi all,

I put together this script to show table locks, users' transactions, user objects being accessed, blocked objects, blocked sessions and the blockers details. I appreciate if you test the script and let me know if it requires any amendment and if I have omitted something. Please feel free to suggest.

Thanks


SET LINESIZE 145
SET PAGESIZE 66

COLUMN owner FORMAT a5 HEADING 'Owner'
COLUMN object_type FORMAT a10 HEADING 'Type'
COLUMN object_name FORMAT a25 HEADING 'Name'
COLUMN Object FORMAT a27 HEADING 'Object'
COLUMN locked_mode FORMAT a20 HEADING 'Locked Mode'
COLUMN sid FORMAT 999999 HEADING 'DB SID'
COLUMN username FORMAT a15 HEADING 'Locker'
COLUMN osuser FORMAT a10 HEADING 'O/S User'
COLUMN logon_time HEADING 'Login Time'

prompt
Prompt +-----------------------------------------------------------------+
Prompt | Table Locking Info FROM v$locked_object, dba_objects, v$session |
Prompt +-----------------------------------------------------------------+

SELECT
SUBSTR(b.owner, 1, 8)||'.'||SUBSTR(b.object_name, 1, 18) "Object"
, b.object_type object_type
, DECODE(a.locked_mode
, 0, 'NONE'
, 1, 'NULL'
, 2, 'ROW SHARE'
, 3, 'ROW EXCLUSIVE'
, 4, 'SHARE'
, 5, 'SHARE ROW EXCLUSIVE'
, 6, 'EXCLUSIVE') locked_mode
, a.session_id "DB Sid"
,a.oracle_username "Locker"
, a.os_user_name osuser
, TO_CHAR(c.logon_time,'YYYY/MM/DD HH24:MI:SS') logon_time
FROM
v$locked_object a
, dba_objects b
, v$session c
WHERE
a.object_id = b.object_id
AND a.session_id = c.sid
ORDER BY
b.owner
, b.object_type
, b.object_name
/

COLUMN UserName FORMAT a20 HEADING 'UserName'
COLUMN "DB Sid" FORMAT 999999 HEADING 'DB Sid'
COLUMN "Unix Pid" FORMAT 99999999
COLUMN "Trnx_start_time" FORMAT a19
COLUMN "Current Time" FORMAT a19
COLUMN "Elapsed(mins)" FORMAT 999999999.99
COLUMN "Undo Name" FORMAT a09
COLUMN "Used Undo Blks" FORMAT a13
COLUMN "Used Undo Size(Kb)" FORMAT a17
COLUMN "Logical I/O(Blks)" FORMAT 99999999999999999
COLUMN "Logical I/O(Kb)" FORMAT 999999999999999
COLUMN "Physical I/O(Blks)" FORMAT 999999999999999999
COLUMN "Physical I/O(Kb)" FORMAT 999999999999999999

Prompt +-------------------------------------------------------------------------------------------------+
Prompt | User Transactions Info FROM v$session, v$transaction, dba_rollback_segs, v$parameter, v$process |
Prompt +-------------------------------------------------------------------------------------------------+

SELECT
a.username "UserName"
, a.sid "DB Sid"
, e.spid "Unix Pid"
, TO_CHAR(TO_DATE(b.start_time,'mm/dd/yy hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss') "Trnx_start_time"
, TO_CHAR(sysdate,'yyyy/mm/dd hh24:mi:ss') "Current Time"
, ROUND(60*24*(sysdate-to_date(b.start_time,'mm/dd/yy hh24:mi:ss')),2) "Elapsed(mins)"
, c.segment_name "Undo Name"
, TO_CHAR(b.used_ublk*d.value/1024) "Used Undo Size(Kb)"
, TO_CHAR(b.used_ublk) "Used Undo Blks"
, b.log_io "Logical I/O(Blks)"
, b.log_io*d.value/1024 "Logical I/O(Kb)"
, b.phy_io "Physical I/O(Blks)"
, b.phy_io*d.value/1024 "Physical I/O(Kb)"
, a.program
FROM
v$session a
, v$transaction b
, dba_rollback_segs c
, v$parameter d
, v$process e
WHERE
b.ses_addr = a.saddr
AND b.xidusn = c.segment_id
AND d.name = 'db_block_size'
AND e.ADDR = a.PADDR
ORDER BY 4
/

Prompt +-------------------------------------------------------------------------------+
Prompt | Objects owned by users being accessed FROM v$session, v$transaction, v$Access |
Prompt +-------------------------------------------------------------------------------+

COLUMN TYPE FORMAT a24 HEADING 'Type'
COLUMN object FORMAT a30 HEADING 'Object'
COLUMN UserName FORMAT a20 HEADING 'UserName'
COLUMN "DB Sid" FORMAT 999999

SELECT
a.username "UserName"
, a.sid "DB Sid"
, SUBSTR(c.owner, 1, 8)||'.'||SUBSTR(c.object, 1, 18) "Object"
, c.type "Type"
FROM v$session a
, v$transaction b
, v$Access c
WHERE
a.saddr = b.ses_addr
AND c.sid = a.sid
AND c.owner NOT IN ('SYS')
ORDER BY a.sid, c.type
/

Prompt +-------------------------------------------+
Prompt | Blocked objects from V$LOCK and SYS.OBJ$ |
Prompt +-------------------------------------------+

COLUMN BLOCKED_OBJ FORMAT a35 HEADING 'Blocked Object'

SELECT /*+ ORDERED */
l.sid
, l.lmode
, TRUNC(l.ctime/60) min_blocked
, u.name||'.'||o.NAME blocked_obj
FROM (SELECT *
FROM v$lock
WHERE type='TM'
AND sid IN (SELECT sid
FROM v$lock
WHERE block !=0 )) l
, sys.obj$ o
, sys.user$ u
WHERE o.obj# = l.ID1
AND WNER# = u.user#
/

Prompt +-------------------------------+
Prompt | Blocked sessions from V$LOCK |
Prompt +-------------------------------+

SELECT /*+ ORDERED */
blocker.sid blocker_sid
, blocked.sid blocked_sid
, TRUNC(blocked.ctime/60) min_blocked
, blocked.request
FROM (SELECT *
FROM v$lock
WHERE block != 0
AND type = 'TX') blocker
, v$lock blocked
WHERE blocked.type='TX'
AND blocked.block = 0
AND blocked.id1 = blocker.id1
/

CLEAR COLUMN
CLEAR BREAKS
SET LINES 80
SET TRIMS on
SET PAGES 9000
COLUMN sql_text FORMAT a70 word_wrapped
COLUMN sid FORMAT a10 NOPRINT new_value n_sid
COLUMN serial FORMAT a10 NOPRINT new_value n_serial
COLUMN username FORMAT a20 NOPRINT new_value n_username
COLUMN machine FORMAT a20 NOPRINT new_value n_machine
COLUMN osuser FORMAT a20 NOPRINT new_value n_osuser
COLUMN process FORMAT a20 NOPRINT new_value n_process
COLUMN action FORMAT a45 NOPRINT new_value n_action

Prompt +--------------------------------------------------------+
Prompt | Blockers session details from V$SESSION and V$SQLTEXT |
Prompt +--------------------------------------------------------+

BREAK ON sid ON serial ON username ON process ON machine ON actiON skip page

ttitle -
"Sid .......... : " n_sid -
skip 1 -
"Serial ....... : " n_serial -
skip 1 -
"Username ..... : " n_username -
skip 1 -
"Machine ...... : " n_machine -
skip 1 -
"OSuser ....... : " n_osuser -
skip 1 -
"Process ...... : " n_process -
skip 1 -
"Action ....... : " n_action -

SELECT /*+ ORDERED */
sid,serial# serial,username,machine,osuser,process,module||' '||action action,sql_text
FROM v$session ses, v$sqltext txt
WHERE txt.address(+) = ses.sql_address
AND txt.hash_value(+) = ses.sql_hash_value
AND ses.sid IN (SELECT sid
FROM v$lock
WHERE block != 0
AND type = 'TX')
ORDER BY ses.sid,txt.piece
/

 

[nassrin5]

Very useful scripts dbalearner. Thanks