Tuesday, April 23, 2019

Oracle : Check who is locking User


There are different ways to find who is locking the USER depending on what level of auditing is set on the database


For OS level Auditing


SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      OS

$cd <audit-log-location>
$cat *.aud | grep -i <user> | grep 1017


For DB level Auditing


SQL> show parameter audit_trail

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      DB


set lines 200
set pages 200
column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/