Oracle view blocking (lock) information

Time:2021-8-29

The SQL part for monitoring is sorted into a column to output the main content. I don’t like to disassemble it myself or add other column information that needs to be used.

Resolution:
1. Only the blocked and blocked persons are output, and the blocking time > = 60s;
2. Chr (10) line feed output, the effect can be seen in the sqlplus command line, and there is no use in PLSQL dev;
3. In consideration of execution efficiency, SQL is not searched_ Text, only SQL_ ID,Prev_ SQL_ ID, based on which the problem point of the auxiliary positioning program is located;
4. Finally, generate a statement to find locked data.

SELECT 'Blocker_SID: ' || A.SID || ', ' || A_S.SCHEMANAME || ', From: ' ||
       A_S.MACHINE || ', ' || A_S.PROGRAM || ', Cur_SQL: ' || A_S.SQL_ID ||
       ',Prev_SQL: ' || A_S.PREV_SQL_ID || ', Status:' || A_S.STATUS ||
       ', Lock_Time: ' || A.CTIME || 's.' || CHR(10) || ' -> Locked_SID: ' ||
       B.SID || ', Blocked_SQL: ' || B_S.SQL_ID || ' , Locked_ON: ' ||
       OBJ.OWNER || '.' || OBJ.OBJECT_NAME || ', Lock_Mode: ' ||
       DECODE(A.LMODE,
              0,
              '0,none',
              1,
              '1,NULL',
              2,
              '2,row-S(SS)',
              3,
              '3,row-X(SX)',
              4,
              '4,share(S)',
              5,
              '5,S/Row-X(SSX)',
              6,
              '6,exclusive(X)') || CHR(10) || ' -> Locked_data_query_SQL: ' ||
       (DECODE(OBJ.OBJECT_TYPE,
               'TABLE',
               'SELECT * FROM ' || OBJ.OWNER || '.' || OBJ.OBJECT_NAME ||
               ' WHERE ROWID = ''' ||
               DBMS_ROWID.ROWID_CREATE(1,
                                       OBJ.DATA_OBJECT_ID,
                                       B_S.ROW_WAIT_FILE#,
                                       B_S.ROW_WAIT_BLOCK#,
                                       B_S.ROW_WAIT_ROW#) || ''';',
               NULL)) AS BLOCK_DETAIL
  FROM GV$LOCK     A,
       GV$LOCK     B,
       GV$SESSION  A_S,
       GV$SESSION  B_S,
       DBA_OBJECTS OBJ
 WHERE A.ID1 = B.ID1
   AND A.ID2 = B.ID2
   AND A.CTIME >= 60
   AND A.BLOCK > 0
   AND B.REQUEST > 0
   AND A.SID = A_S.SID
   AND A.INST_ID = A_S.INST_ID
   AND B.SID = B_S.SID
   AND B.INST_ID = B_S.INST_ID
   AND B_S.ROW_WAIT_OBJ# = OBJ.OBJECT_ID(+)
 ORDER BY A.INST_ID, A.SID;

Attach SQL based_ ID lookup SQL_ Text statement:

SELECT Q.SQL_ID, Q.SQL_TEXT, Q.SQL_FULLTEXT
  FROM V$SQL Q
 WHERE Q.SQL_ID = '&SQL_ID';
--After execution, the SQL variable_ Enter the SQL ID extracted above_ ID is enough

Recommended Today

The selector returned by ngrx store createselector performs one-step debugging of fetching logic

Test source code: import { Component } from ‘@angular/core’; import { createSelector } from ‘@ngrx/store’; export interface State { counter1: number; counter2: number; } export const selectCounter1 = (state: State) => state.counter1; export const selectCounter2 = (state: State) => state.counter2; export const selectTotal = createSelector( selectCounter1, selectCounter2, (counter1, counter2) => counter1 + counter2 ); // […]