-- Audit Oracle Connections
alter system set audit_trail='DB' scope =spfile;

-- bounce the database and then:

audit connect;


-- Display userids that have logged on in the last 7 days
select userid, count(*) from sys.aud$ where ntimestamp# > sysdate -7 group by userid order by 2;

-- Display oldest audit rec
select min(ntimestamp#) from sys.aud$;

-- Delete audit recs older than 365 days
delete sys.aud$ where ntimestamp# < sysdate -30;
commit;


-- Display Fine-Grained Policies
SELECT * FROM dba_audit_policy_columns;
-- Drop a policy
EXEC DBMS_FGA.DROP_POLICY(object_schema=>'HOPE',object_name=>'ORG_FIN_STMNT', policy_name=>'POL_ORGFINSTMT_TOTALREVCDN');

-- Display user that locked an account in 11g
set lines 130
SET PAGES 200
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a20
 
select OS_USERNAME
      ,t.USERNAME,USERHOST
      ,to_char(t.timestamp,'MM-DD-YYYY HH24:MI:SS') LOCK_DATE
      , decode (t.returncode, '0', 'Successful logon',
               '1017', 'wrong combination user/password',
               '2004', 'Security violation',
               '28000', 'user locked',
               '28001', 'Password expired') STATUS
from dba_audit_trail t, dba_users u
where t.timestamp = u.LOCK_DATE;