----创建存储过程（预警用户登录锁定、过期及其审计信息）

CREATE OR REPLACE PROCEDURE CUX_USERLOCK_ALERT as
  SUBJECT       varchar2(100);
  INFO_USER1    varchar2(4000);
  INFO_USER2    varchar2(4000);
  v_lock_status number;

BEGIN
  ------根据用户状态是否锁定

 select count(1)
 into v_lock_status
 from dba_users a
 where a.username in
       ('SCOTT')
 and a.account_status in ('LOCKED', 'LOCKED(TIMED)', 'EXPIRED',
                    'EXPIRED & LOCKED(TIMED)', 'EXPIRED & LOCKED');

  ---输出锁定用户的相关信息（锁定时间、审计信息）

 if v_lock_status <> 0 then
    SUBJECT    := 'ERP-SIT-DB User Lock Status Alert Info';
    INFO_USER1 := 'Oracle EBS数据库用户锁定相关信息:' || '%' || chr(13) || chr(10) ||
                  '用户名       用户状态      锁定时间      用户主机       操作对象     动作';
    for cur1 in (select dat.username,
                        du.account_status,
                        du.lock_date,
                        dat.userhost,
                        dat.timestamp,
                        dat.obj_name,
                        dat.action_name
                   from dba_audit_trail dat, dba_users du
                  where (dat.obj_name = du.username or
                        dat.username = du.username)
                    and trunc(dat.timestamp) = trunc(sysdate)
                    and du.username in ('SCOTT')
                    and du.account_status in
                        ('LOCKED', 'LOCKED(TIMED)', 'EXPIRED',
                         'EXPIRED & LOCKED(TIMED)', 'EXPIRED & LOCKED'))

loop
INFO_USER2 := cur1.username || ','|| cur1.account_status || ','|| cur1.lock_date || ','||  cur1.userhost || ','|| cur1.timestamp 
|| ','||  cur1.obj_name || ','||  cur1.action_name;
insert into t_audit1 values(INFO_USER2);
commit;
--INFO_USER1 := INFO_USER1 || chr(13) || chr(10) || INFO_USER2;

end loop;
    --send_mail(SUBJECT,INFO_USER1); ---发送邮件过程
 end if;
end;

---显示授权

grant select to dba_users to system;
grant select to  dba_audit_trail dat to system;

----创建job（每个两小时执行一次）

begin
  sys.dbms_job.submit(job => :job,
                 what => 'begin CUX_USERLOCK_ALERT; end;',
                 next_date => to_date('09-06-2014 18:25:34', 'dd-mm-yyyy hh24:mi:ss'),
                 interval => 'SYSDATE+2/24');
  commit;
end;
/

--转自