How to find the user name in Oracle who is accessing particular object.

Hi Guy's,

Its very easy to find the 'User-Name' if you want to kill his session.

In my prod database where indexes are not getting rebuild even after giving the 'Rebuild-Online' option.
because this table is not getting released by the user. I already know the table name which has hold the index rebuild and then killed the sessions.


SQL> select object,owner,sid from v$access where object='MPMCSTK '


OBJECT       OWNER         SID
------------ ----------------------------------
MPMCSTK     DAVID          268
MPMCSTK     DAVID         584
MPMCSTK     JOHN          1413
MPMCSTK     KETE          1474
MPMCSTK     CATHY        842

28 rows selected.


SQL> select USERNAME,inst_id,sid,serial# from gv$session where username='DAVID';

USERNAME      INST_ID        SID          SERIAL#
------------------------------------------------------------------------
DAVID                      1              268          6921
DAVID                      1              584          2427


SQL> alter system kill session '268,6921,@1' immediate;

SQL> alter system kill session '584,2427,@1' immediate;





Happy Learning....