v$access

V$ACCESS显示有关当前施加在库缓存对象上的锁的信息。

这些锁是为了确保它们在执行SQL时不会从库缓存中过时

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.

SQL> desc v$access
 Name       Null?    Type
 ---------- -------- ----------------
 SID                 NUMBER
 OWNER               VARCHAR2(64)
 OBJECT              VARCHAR2(1000)
 TYPE                VARCHAR2(64)

Column	Datatype	Description
SID	NUMBER	Session number that is accessing an object
OWNER	VARCHAR2(64)	Owner of the object
OBJECT	VARCHAR2(1000)	Name of the object
TYPE	VARCHAR2(64)	Type identifier for the object
CON_ID	NUMBER	The ID of the container to which the data pertains. Possible values include:0: This value is used for rows containing data that pertain to the entire multitenant container database (CDB). This value is also used for rows in non-CDBs.1: This value is used for rows containing data that pertain to only the rootn: Where n is the applicable container ID for the rows containing data

v$access与v$locked_object 的区别

v$locked_object定义：

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定义

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.

简单点说，只要存在在library cache中，通过表v$access就能够查看到使用到的表，存储过程以及cursor。V$LOCKED_OBJECT只能查看到相关的表。此外，当语句执行完之后，就会消失，但是V$LOCKED_OBJECT必须提交或者rollback之后才会消失。

会话2279

-- 创建测试表
sys@testdba(2279)> create table test_t(id int);
Table created.

-- 执行存储过程
sys@testdba(2279)> set serveroutput on

begin
for i in 1..10 loop
insert into test_t values(i);
dbms_output.put_line(i);
dbms_lock.sleep(10);
end loop;
end;
/

当该session不执行其他语句时，在其他session会查看任然存在在v$access表中，当该session执行其他语句时，v$access表中就会消失。

sys@testdba(766)> select * from v$access where object='TEST_T';
 SID OWNER OBJECT   TYPE
-------------------------
2279 SYS   TEST_T   TABLE

sys@testdba(766)> SELECT * FROM V$LOCKED_OBJECT;
    XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME
---------- ---------- ---------- ---------- ---------- ------------------------------
OS_USER_NAME                   PROCESS                  LOCKED_MODE
------------------------------ ------------------------ -----------
         7         33        715      87360       2279 SYS
oracle                         68254                              3

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

sys@testdba(2279)> commit;

Commit complete.


-- 766会话查询
sys@testdba(766)> select * from v$access where object='TEST_T';
 SID OWNER OBJECT   TYPE
-------------------------
2279 SYS   TEST_T   TABLE

sys@testdba(766)> SELECT * FROM V$LOCKED_OBJECT;

no rows selected