[20230601]V$SESSION.row_wait_obj#字段.txt

--//如果出现行锁，V$SESSION.row_wait_obj#记录的是object_id,而不是data_object_id.
--//通过一个例子验证，加强自己的记忆。

1.环境：
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SCOTT@test01p> create table dept1 as select * from dept ;
Table created.

SCOTT@test01p> @ o2 dept1
owner object_name object_type SUBOBJECT_NAME status           OID      D_OID CREATED             LAST_DDL_TIME
----- ----------- ----------- -------------- --------- ---------- ---------- ------------------- -------------------
SCOTT DEPT1       TABLE                      VALID          30033      30033 2023-06-01 21:52:21 2023-06-01 21:50:26

--//正常建立的表object_id=data_object_id,如果truncate或者move后，data_object_id会发生变化。
SCOTT@test01p> alter table dept1 move online;
Table altered.

SCOTT@test01p> @ o2 dept1
owner object_name object_type SUBOBJECT_NAME status           OID      D_OID CREATED             LAST_DDL_TIME
----- ----------- ----------- -------------- --------- ---------- ---------- ------------------- -------------------
SCOTT DEPT1       TABLE                      VALID          30033      30034 2023-06-01 21:52:21 2023-06-01 21:55:16

2.测试：
--//session 1:
SCOTT@test01p> @ spid
 SID    SERIAL# PROCESS   SERVER    SPID PID  P_SERIAL# C50
---- ---------- --------- --------- ---- ---- ---------- --------------------------------------------------
 252      65058 6304:1124 DEDICATED 6048  27          7 alter system kill session '252,65058' immediate;

SCOTT@test01p> select * from dept1 where deptno=20 for update;
    DEPTNO DNAME                LOC
---------- -------------------- -------------
        20 RESEARCH             DALLAS

--//session 2:
SCOTT@test01p> @ spid
 SID    SERIAL# PROCESS   SERVER    SPID PID  P_SERIAL# C50
---- ---------- --------- --------- ---- --- ---------- --------------------------------------------------
 105      48994 1564:5860 DEDICATED 8860  41         42 alter system kill session '105,48994' immediate;

SCOTT@test01p> select * from dept1 where deptno=20 for update;

--//挂起!!

3.使用ashtop观察：
SYS@test> @wcx &1min

-- Display ASH Wait Chain Signatures script v0.7 by Tanel Poder ( http://blog.tanelpoder.com )
%This     SECONDS        AAS WAIT_CHAIN                                                                                               FIRST_SEEN          LAST_SEEN
------ ---------- ---------- -------------------------------------------------------------------------------------------------------- ------------------- -------------------
  44%          38         .6 -> 252,65058,@1=>105,48994,@1=>enq: TX - row lock contention -> [idle blocker 1,252,65058 (sqlplus.exe)] 2023-06-01 21:59:37 2023-06-01 22:00:14
  10%           9         .2 -> ,,@=>180,51168,@1=>db file sequential read                                                            2023-06-01 22:00:04 2023-06-01 22:00:13
   8%           7         .1 -> ,,@=>257,9548,@1=>                                                                                    2023-06-01 22:00:06 2023-06-01 22:00:14
...
19 rows selected.

SYS@test> @ashtop SESSION_ID,SESSION_SERIAL#,CURRENT_OBJ#,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW# "event='enq: TX - row lock contention'" &1min
    Total                                                                                                                                             Distinct Distinct
  Seconds     AAS %This   SESSION_ID SESSION_SERIAL# CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW# FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ---------- --------------- ------------ ------------- -------------- ------------ ------------------- ------------------- ---------- --------
       60     1.0  100% |        105           48994        30033            11            315            1 2023-06-01 22:13:38 2023-06-01 22:14:37          1       60

--//当前记录的CURRENT_OBJ=30033,对应object_id.我这里查询v$active_session_history,实际上对应V$SESSION.row_wait_obj#.

SCOTT@test01p> select data_object_id from dba_objects where object_id=30033;
DATA_OBJECT_ID
--------------
         30034


SCOTT@test01p> select dbms_rowid.rowid_create(1,30034,11,315,1)  from dual ;
DBMS_ROWID.ROWID_C
------------------
AAAHVSAALAAAAE7AAB        

SCOTT@test01p> @ashtop "SESSION_ID,SESSION_SERIAL#,dbms_rowid.rowid_create(1,(select data_object_id from dba_objects where object_id=CURRENT_OBJ#),CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW#) " "event='enq: TX - row lock contention'" &1min
        SESSION_ID,SESSION_SERIAL#,dbms_rowid.rowid_create(1,(select data_object_id from dba_objects where object_id=CURRENT_OBJ#),CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW#)
                                                              *
ERROR at line 78:
ORA-22818: subquery expressions not allowed here

SCOTT@test01p> @ashtop "SESSION_ID,SESSION_SERIAL#,dbms_rowid.rowid_create(1,30034,CURRENT_FILE#,CURRENT_BLOCK#,CURRENT_ROW#) " "event='enq: TX - row lock contention'" &1min
    Total                                                                                                         Distinct Distinct
  Seconds     AAS %This   SESSION_ID SESSION_SERIAL# DBMS_ROWID.ROWID_C FIRST_SEEN          LAST_SEEN           Execs Seen  Tstamps
--------- ------- ------- ---------- --------------- ------------------ ------------------- ------------------- ---------- --------
       60     1.0  100% |        182            5689 AAAHVSAALAAAAE7AAB 2023-06-02 21:23:28 2023-06-02 21:24:27          1       60

--//另外CURRENT_FILE#记录的绝对文件号,许多情况下等于相对文件号.