Oracle User Login Lock Wrong Attempt details

select username,osuser,machine,blocking_session from v$session where username='LDBO';

select username,os_username,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",terminal,
---UTL_INADDR.get_host_address(terminal) "IP",
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
from dba_audit_session where trim(Timestamp) > trunc(sysdate-1)
and username='AHE'
-----and os_username like '%UNICON\%'
order by timestamp desc;

select username,password,account_status,to_char(lock_date, 'Dy DD-Mon-YYYY HH24:MI:SS') lock_date,expiry_date
from dba_users where account_status like '%LOCKED%' order by 3 desc,lock_date desc;

select distinct username "USER ID",osuser,machine,UTL_INADDR.get_host_address(terminal)"System IP", decode(username,'USSB3409','ANILKUMAR','USSB0065','GAURAVSINGH','USSB0737','KAVITA', 'USSB0580','MAHINDERSINGH', 'USSB0624','RAJENDERSINGH','USSB0625','RAKESHKUMAR','UIPL6716','RAKESHLAL','USSB0500','RUPINDERKAUR','UIPL6713','SHASHINATH','USSB0502','SURENDERKUMAR','UIPL6715','VIRENDERSINGH','UIPL6714','SANJAYKUMARSISODIA','UIPL7249','BHAVYASINGH','UFIPL00152','GOVINDGUPTA','UIPL7244','INDIRARAWAT','USSB2693','JYOTI','UIPL7258','MANTUKUMARSINGH','USSB0080','PRIYADAS','USSB2720','RAHUL','UIPL7210','ROBINTYAGI','UFIPL00154','ROHITGUPTA','USSB1740','SHAKSHI','UIPL7259','VINODKUMAR','USSB0991','ARJUNSINGH','UFPL00058','DHEERAJTANEJA','UFIPL00151','PAWANKUMARJINDAL','UIPL7759','PRABHAKARSINGH','USSB0948','PRIYANKARANA','USSB6549','RAJESHSHARMA.','USSB6788','RAMKRISHANKUMAR','USSB6790','ROSHANKUMAR','USSB0550','SACHINSUGANDHA','UIPL7261','SHOBHITAGARWAL','USSB1470','SIDDHARTHBHATIA','USSB6996','KANIKAKHURANA','UFIPL00153','DEVESH','UIPL10283','SWATISALUJA','UIPL10287','NITINGOYAL','UIPL9767','GAURAV','UFIPL00156','AMITKUMARSINGHAL','UIPL10305','PRANJEETKAUSHIK','UFPL00173','PUNEETSHARMA','UIPL10672','SUMITRAJORA','UFPL00188','DEEPAKGUPTA','UFPL00195','VINEETSABHLOK','USSB0019','PRASHANTUPADHYAY','USSB0022','NEERAJGROVER','USSB0109','SAURABHAATRE','USSB0626','SHAHABUDDIN','USSB2701','NITESH','UCPL0491','POONAMSINGH','UCPL0116','DURGESHKESHRI','USSB0211','SAURABHCHAUDHARY','USSB0431','VIRENDERSINGHCHANDEL','USSB5498','AKANSHARAWAL','USSB7030','SATYANARAYANA','UIPL6793','JAGDEEPSINGH','USSB3700','JAYAACHAR','UIPL6778','NEERAJSHARMA','USSB6047','NEHAGOEL','UIPL6798','ROHANSHARMA','UIPL6828','SACHINTYAGI','USSB4173','SEEMAGAMBHIR','USSB4766','SHAKSHIARORA','UCPL0117','SHAMPAMUKHERJEE','UIPL6736','SONIABENWAL','USSB6621','TANUSHREESOMANI','USSB1236','ASHIMAARORA','UIPL6771','SHELLY','USSB5708','KULDEEPKAUR','UIPL6801','SHWETA','UIPL6800','ROHITLUTHRA','USSB7428','AMRESHKUMAR','UIPL10160','SONIASABHARWAL','USSB7444','ANILSHARMA','UIPL10515','SAPNAGUPTA','UIPL10402','KHUSHALRAZDAN','UIPL6742','GITTUKATARIA','UIPL6746','MUNNASINGH','USSB0013','AMITTYAGI','UIPL6722','DEEPANSHUMALHOTRA','USSB5695','HARENDRASHARMA','UIPL6727','MANISHVERMA','USSB1112','PRATIMASINGH','USSB5374','PULKIT','USSB1253','RAHULTYAGI','USSB5274','VARUNKANT','UFPL00178','SUBHASHCHANDRAGEHLOT','UFPL00028','SUBHASHSHARMA','USSB6901','KSHITIJR.GUPTA','UCPL0154','RACHITJAIN','USSB6097','SHEKHARSAXENA','UIPL6712','YESHPALTHAKUR','USSB6002','SANDHYASHARMA','UFIPL00130','ANJALIMUKHIJA','UCPL0111','AMITUPADHYAY','USSB6045','RAJAN','UFIPL00136','SANDEEPSIR','USSB0064','AMITJAIN','UIPL6831','ANKITASRIVASTAVA','USSB6805','SHASHIBHUSHAN','USSB6950','SUNIIGABA','USSB7222','NILESHTRIPATHI','USSB0849','OMPRAKASHBANDERWAL','USSB0458','ROHTASAGARWAL','RMS','RMS','USSB1366','NEETARASTOGI','USSB0256','ANANDJOSHI','USSB1032','SATISHSHARMA','USSB1113','SUNILKUMARSINGHAL','UIPL6721','VISHALGUPTA','UIPL6724','YATENDRASINGHBISHT','USSB6241','KULDEEPSHARMA','USSB5495','RAGHIBHUSAIN','USSB1031','TARUNCHHOKRA','UIPL6717','NEERAJKAUSHIK','USSB6508','ACHINANAND','USSB7530','KHYATIADLAKHA','USSB7565','BHUWANSHARMA','USSB7550','NEHASINGH','LDBO','DBA') Name
from v$session where program ='ld.exe' order by 5;

select username,password,account_status,lock_date, to_char(expiry_date, 'Dy DD-Mon-YYYY HH24:MI:SS') expir_date
from dba_users where account_status like '%EXPIRED%' order by expiry_date desc;

select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name, decode(ses_actions,'---------S------','SELECT','----------S-----','UPDATE',ses_actions) action,action_name
from dba_audit_trail
where trim(Timestamp)=trunc(sysdate)
order by 4 desc;


select username,os_username,terminal,to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name
,decode(returncode,1917,'Grant Revoke',1918,'ALter',1920,'Creation',returncode) Status
from dba_audit_exists
order by timestamp desc;

select firmnumber,oowncode as username,cclientlevelpassword as password from clemaildetail where oowncode='DP122';
SELECT username, terminal, to_char (timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS'),
decode(returncode,0,'Successful',1017,'WrongAttempt',28000,'Locked',28009,'SYS Login',1005,'Fail_NULL',28001,'EXPIRED',28031,'Roles_Exceeded',returncode) Login_Status
FROM dba_audit_session
WHERE returncode <> 0 AND not exists (SELECT 'x' FROM dba_users WHERE dba_users.username=dba_audit_session.username)
and trim(Timestamp)=trunc(sysdate)
order by timestamp desc;


select username,os_username,terminal "System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",obj_name,action_name from dba_audit_object where trim(Timestamp)=trunc(sysdate) order by timestamp desc;

select username,os_username,
UTL_INADDR.get_host_address(terminal)"System IP",to_char(timestamp, 'Dy DD-Mon-YYYY HH24:MI:SS') "Time",
obj_name,action_name from dba_audit_statement
--where
----trim(Timestamp)=trunc(sysdate)
--obj_name='UIPL6713'
order by timestamp desc;

select * from dba_stmt_audit_opts;

select * from dba_priv_audit_opts;
select * from dba_audit_object where obj_name='LDBO' order by timestamp desc;


select distinct * from TBLAUDITUSERLOGONDETAILS
where cusername not in ('SYS','CLLVL')
and cusername='LDBO'
and trim(dlogonday)=trunc(SYSDATE)
order by dlogonday desc,clogontime desc;

Select Substr(Upper(User_audit_trail.Os_Username),1,30) as cOS_Username, Rpad(' ',10) as Oowncode, Substr(User_audit_trail.Username,1,30) as cUsername,User_audit_trail.Userhost as cUserhost, Upper(Substr(User_audit_trail.Terminal,1,30)) as cTerminal,User_audit_trail.Timestamp as dTimeStamp, nvl(User_audit_trail.Obj_name,' ') as cObjectName,User_audit_trail.Action_name as cActionname, nvl(User_audit_trail.Sys_Privilege,' ') as cSysPrivilege,nvl(User_audit_trail.Grantee,' ') as cGrantee, nvl(User_audit_trail.Priv_Used,' ') as cPrivUsed From User_audit_trail User_audit_trail
Where User_audit_trail.Os_Username not in ('SYSTEM','SYSMAN','DBSNMP')
and User_audit_trail.Username='LDBO'
and trim(User_audit_trail.Timestamp)=trunc(sysdate)
order by timestamp;


Select Dba_Users.Username as cUsername,Dba_Users.Profile as cProfile, Dba_Users.Account_status as cAccountstatus,Dba_Users.Lock_date as dLockDate,Dba_Users.Expiry_Date as dExpiryDate, Dba_users.Created as dCreationDate
From Dba_Users Dba_Users
Where Dba_users.Username not in ('SYS', 'SYSTEM', 'DBSNMP', 'TSMSYS', 'OUTLN', 'ORACLE_OCM','MGMT_VIEW','SYSMAN', 'WMSYS', 'DIP')
and Dba_Users.Created>='13-MAR-09'
--and trim(dlockdate)!=' '
---and trim(dexpirydate)!=' '
and Dba_Users.Created<=trunc(sysdate) order by 5 desc, 4 desc;

-----------------------------