Wednesday, October 25, 2017

dba_locks does not show for both instances

Problem: While working on blocking session script below, I figured out that blocking sessions are not displayed in both nodes of the rac, exadata.

Cause: Usual suspect in the from clause are dba_objects, gv$session, and dba_locks. I just look into the dba_locks, because dba_objects is just related with database physical structure which should be common in RAC. gv$session also cannot be, because it is gv$, it must surely include common information among instances again. So, it must be for dba_locks, and there it is,

desc DBA_LOCKS
SESSION_ID NUMBER
LOCK_TYPE VARCHAR2(26)
MODE_HELD VARCHAR2(40)
MODE_REQUESTED VARCHAR2(40)
LOCK_ID1 VARCHAR2(40)
LOCK_ID2 VARCHAR2(40)
LAST_CONVERT NUMBER
BLOCKING_OTHERS VARCHAR2(40)

Solution:

There must be alternative real RAC aware version of it. It is GV$LOCK, some small differences in column names, and values, it has same information, and it shows more for both nodes.

desc GV$LOCK
INST_ID NUMBER
ADDR RAW(8)
KADDR RAW(8)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
CTIME NUMBER
BLOCK NUMBER
CON_ID

Before my script looks like this:


After making modifications on column names and, associated values, my script turns out to be as follows:



Windows (powershell) counterparts of Linux commands

You may find Windows mostly powershell equivalent of frequently used Linux commands here. I will update this post, with newer ones by the ti...