How to know which objects are being accessed by a user

You can track what users are doing currently in the database. You can get the object name as well as the operating system user name, the host name, the type of objects they are accessing and many more.

Below is the query. You can query from more columns if you like. Also to run the query you need access to the v$access and v$session view.

In the example I have shown what user TEST1 and TEST2 is doing in the database.

SQL> SET LINESIZE 200
SQL> SET VERIFY OFF
SQL> COL object FOR A30
SQL> SELECT a.object,
 2         a.type,
 3         a.sid,
 4         b.username,
 5         b.osuser,
 6         b.program
 7  FROM   v$access a
 8  JOIN   v$session b ON (a.sid = b.sid)
 9  AND    a.owner = UPPER('&SCHEMA') ORDER BY a.object;
Enter value for schema: TEST1


OBJECT                         TYPE             SID USERNAME     OSUSER             PROGRAM
------------------------------ ---------        ---- ---------- ------------------ ------------
DBMS_APPLICATION_INFO          CURSOR           149 TEST2        ARJUPC\Arju       sqlplus.exe
DUAL                           CURSOR           159 TEST1        ARJUPC\Arju       sqlplus.exe
DUAL                           CURSOR           149 TEST2        ARJUPC\Arju       sqlplus.exe
TAB1                           TABLE            159 TEST1        ARJUPC\Arju       sqlplus.exe

SQL> /
Enter value for schema: TEST2


OBJECT                         TYPE          SID USERNAME     OSUSER             PROGRAM
------------------------------ ----------- ---------- ------------ ------------------ ------------
T                              TRIGGER       159 TEST1        ARJUPC\Arju       sqlplus.exe
TAB1                           TABLE         159 TEST1        ARJUPC\Arju       sqlplus.exe

SQL>