Thursday, 11 August 2016

SQL Query to List all objects being accessed in the schema - Oracle Database

Oracle SQL Query to List all objects being accessed in the schema 

Below given is the script that will display you all the objects that are being accessed by a particular schema:

SET LINESIZE 255
SET VERIFY OFF
COLUMN object FORMAT A30

SELECT a.object,
       a.type,
       a.sid,
       b.serial#,
       b.username,
       b.osuser,
       b.program
FROM   v$access a,
       v$session b
WHERE  a.sid    = b.sid
AND    a.owner  = DECODE(UPPER('&1'), 'ALL', a.object, UPPER('&1'))
AND    a.object = DECODE(UPPER('&2'), 'ALL', a.object, UPPER('&2'))
ORDER BY a.object;

No comments:

Post a comment