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

[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#记录的绝对文件号,许多情况下等于相对文件号.