Tuesday, August 7, 2012

Email Notification for alter user


CREATE OR REPLACE TRIGGER dtr_userEvents
BEFORE ALTER ON SCHEMA
DECLARE
  l_sqlText    ORA_NAME_LIST_T;

BEGIN
  IF ora_dict_obj_type IN ( 'USER') and  SYS_CONTEXT ('USERENV', 'SESSION_USER') in ('SYS','LDBO')
  THEN
BEGIN
  utl_mail.send (
  sender => 'kshitij@apexsoftcell.com',
  recipients => 'kshitij@apexsoftcell.com',
  subject => 'User change  has been made in '||ORA_DATABASE_NAME||' database.',
  message => 'User '||ORA_LOGIN_USER||' had run '||ORA_SYSEVENT|| ' on '||ORA_DICT_OBJ_TYPE||' '||ORA_DICT_OBJ_NAME||' in '||ORA_DATABASE_NAME||' database.'
  );
  END;
  INSERT INTO ddl_events
  ( SELECT dsq_ddlEvents.NEXTVAL,
           SYSDATE,
           ORA_LOGIN_USER,
           ORA_DICT_OBJ_NAME,
           ORA_DICT_OBJ_OWNER,
           ORA_DICT_OBJ_TYPE,
           ORA_SYSEVENT,
           machine,
           program,
           osuser,
           SYS_CONTEXT('USERENV','IP_ADDRESS')
      FROM SYS.DUAL,
           SYS.V_$SESSION
     WHERE SYS_CONTEXT('USERENV','SESSIONID' ) = audsid(+) );

   FOR l IN 1..ORA_SQL_TXT(l_sqlText) LOOP
    INSERT INTO ddl_events_sql
    ( eventId, sqlLine, sqlText )
    VALUES
    ( dsq_ddlEvents.CURRVAL, l, l_sqlText(l) );
  END LOOP;
 END IF;
END;
/

No comments:

Post a Comment

Followers