文章版权所有 Jusin Hao（luckyfriends） ，支持原创，转载请注明
----创建存储过程（预警用户登录锁定、过期及其审计信息）
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;
/