CREATE OR REPLACE TRIGGER logon_denied_write_alertlog AFTER SERVERERROR ON DATABASE 
DECLARE 
 v_app varchar2(50); 
 v_userid varchar2(50); 
 v_session number(15,0); 
 v_schema_user_name varchar2(50); 
 v_login_time date; 
 v_auditing_session_id number(15,0); 
 v_os_username varchar2(50); 
 v_serial_number varchar2(35);  
v_user_number number(15,0);  
v_client_identifier varchar2(64); 
BEGIN 
 -- ORA-1017: invalid username/password; logon denied 
 IF (IS_SERVERERROR(1017)) THEN 
    select program, username, sid, schemaname, logon_time, audsid, osuser, serial#, user#, client_info   
	  into v_app, v_userid, v_session, v_schema_user_name, v_login_time, v_auditing_session_id, v_os_username, v_serial_number, v_user_number, v_client_identifier 
      from sys.v_$session 
     where sid = to_number(substr(dbms_session.unique_session_id,1 ,4), 'xxxx') 
      and serial# = to_number(substr(dbms_session.unique_session_id,5 ,4), 'xxxx'); 
   
 insert into iqms.mike(program, userid, sessionid, schema_user_name, login_time, auditing_session_id, os_username, good_or_bad, serial_number, user_number, client_identifier)  
               values (v_app, v_userid, v_session, v_schema_user_name, v_login_time, v_auditing_session_id, v_os_username, 'B', v_serial_number, v_user_number, v_client_identifier); 
 END IF; 
END;