The New Home of Oracle-wiki.net
The New Home of Oracle-wiki.net
All things oracle...
All things oracle...
A website for IT Professionals involved with Oracle database.
Embedded Files
Shows Object Accessed for a Given Session ID
--
-- Shows objects accessed in the schema for a given Session ID.
--
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN object FORMAT A32
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
/
Notes on Show Objects Accessed by Session
Notes on Show Objects Accessed by Session
Purpose
Purpose
Shows the objects that a given session is currently accssing.. e.g tables, views, packages or other objects
Variables Used by the Script
Variables Used by the Script
Running the script will prompt for:
ENTER_SCHEMA_NAME - the schema (owner) to filter objects by, entered without quotes, it is upper cased automatically
ENTER_SESSION_ID - the SID of the session to investigate
Best Practice
Best Practice
The oracle documentation carries the following warning
V$ACCESS can be relatively expensive to query on a busy system with a large shared pool because it derives its results from the library cache rather than simple session state. On heavily loaded systems with many active objects, it is best used for a targeted, one-off lookup against a known session or object rather than being run repeatedly in a polling loop.
V$ACCESS can be relatively expensive to query on a busy system with a large shared pool because it derives its results from the library cache rather than simple session state. On heavily loaded systems with many active objects, it is best used for a targeted, one-off lookup against a known session or object rather than being run repeatedly in a polling loop.
Published on
Published 25th September 2026
Page updated
Google Sites
Report abuse