Which session access an object like table or view in Oracle

Find Session access an object like Table or view in Oracle

V$ACCESS gives the SID of the session and the owner, object name, object type and Con_id if you are using Container database.

Check the object access by the session id

COLUMN object FORMAT A30
COLUMN type FORMAT A15
COLUMN sid FORMAT 9999
COLUMN username FORMAT A20
COLUMN osuser FORMAT A10
COLUMN program FORMAT A40
SELECT a.object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = UPPER('&ENTER_SCHEMA_NAME')
AND a.sid = &enter_session_id
ORDER BY a.object
/

Check the Session which accessing the object

COLUMN object FORMAT A30
COLUMN type FORMAT A15
COLUMN sid FORMAT 9999
COLUMN username FORMAT A20
COLUMN osuser FORMAT A10
COLUMN program FORMAT A40
SELECT a.object,
a.type,
a.sid,
b.username,
b.osuser,
b.program
FROM v$access a,
v$session b
WHERE a.sid = b.sid
AND a.owner = UPPER('&ENTER_SCHEMA_NAME')
AND a.object = upper('&OBJECT_NAME')
ORDER BY a.object
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.