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;
/
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment