v$access 与 v$locked_object 的区别

在开发过程中，很多时候都会遇到锁表，我们都还去查看v$locked_object这张表，但是另外一张表v$access这张表有的时候也会使用到，下看一下这两张表的定义：
V$LOCKED_OBJECT lists all locks acquired by every transaction on the system. It show which sessins are holding DML locks (that is, TM-type enqueues) on what objects and in what mode.

V$ACCESS displays information about locks that are currently imposed on library cache objects. The locks are imposed to ensure that they are not aged out of the library cache while they are required for SQL execution.
简单点说，只要存在在LC中，通过表v$access就能够查看到使用到的表，存储过程以及cursor。但是V$LOCKED_OBJECT只能查看到相关的表。此外，当语句执行完之后，就会消失，但是V$LOCKED_OBJECT必须提交或者rollback之后才会消失。
SESSION1:
SQL> set serveroutput on
SQL> select sid from v$mystat where rownum=1;
       SID
----------
        44
SQL> begin
  2    for i in 1..10 loop
  3    insert into sn_qudao.TMP_LIUHC_2 values(i);
  4    dbms_output.put_line(i);
  5    DBMS_LOCK.SLEEP(10);
  6  end loop;
  7  end;
  8  /
1                                                                              
2                                                                              
3                                                                              
4                                                                              
5                                                                              
6                                                                              
7                                                                              
8                                                                              
9                                                                              
10                                                                             
PL/SQL 过程已成功完成。
2. 当该session不执行其他语句时，在其他session会查看任然存在在v$access表中，当该session执行其他语句时，v$access表中就会消失。
SESSION2:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
         6
SQL> select * from v$access where object='TMP_LIUHC_2';       SID OWNER                 OBJECT                    TYPE
---------- ------------- --------------------------- ------------------------
        44 SN_QUDAO             TMP_LIUHC_2                TABLE
SQL> SELECT * FROM V$LOCKED_OBJECT;
    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME             OS_USER_NAME           PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------ ----------------------- ------------ -----------
        10          8      35775      77745         44 SN_QUDAO                    Administrator          3240:4880              3
SESSION1:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
        44
SESSION2:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
         6
SQL> select * from v$access where object='TMP_LIUHC_2';
 
       SID OWNER           OBJECT         TYPE
---------- ---------- -------------- -------------
 
SQL> SELECT * FROM V$LOCKED_OBJECT;
 
    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------ -----------
        10          8      35775      77745         44 SN_QUDAO                       Administrator                  3240:4880              3

3. 当执行session commit之后，在V$LOCKED_OBJECT中也不会找到。

SESSION1:
SQL> select sid from v$mystat where rownum=1;

       SID
----------
        44
SQL> commit;
SQL> SELECT * FROM V$LOCKED_OBJECT;
 
    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME                OS_USER_NAME                   PROCESS      LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------ ----