-- ############################################################
-- # Creator: Nicolas Chabault
-- # Created: 2011/06/14
-- # Name: Trace session logon
-- ############################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ############################################################
-- #
-- # Trace session and store logon data in the table dba_audit_trail
-- # to analyze security or account locking.
-- #
-- ############################################################
Trace session to prevent account lock with the following query:
- Active the parameter audit_trail = db
SQL> alter system set audit_trail=DB scope=spfile + startup force);
- Execute :
SQL> audit create session whenever not successful;
- Execute :
SQL select os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS')
from dba_audit_trail where action_name='LOGON';
-- If you have the response : "no row selected", there's not login authentification problems
-- if you have a login failed you'll have the following lines :
administrator
SYS
PTS/2
07-14-2011 16:00:00
-- To remove the audit (You can also put the parameter "audit_trail = none" for every audits)
SQL> NOaudit create session whenever not successful;