Useful shortcuts for vi editor

Monday, 7 August 2017

e.g. simple DDL audit trigger

e.g. DDL audit trigger

CREATE TABLE SYSTEM.audit_ddl_history
(
osuser varchar2(50),
session_user varchar2(50),
host varchar2(50),
terminal varchar2(50),
object_owner varchar2(50),
obejct_type varchar2(50),
object_name varchar2(50),
sysevent varchar2(50),
audit_date date
);

CREATE OR REPLACE TRIGGER SYSTEM.ALPERDB_AUDIT_HISTORY
   AFTER DDL
   ON DATABASE
begin
if (ora_sysevent='GRANT')
  then
  NULL; -- don't care to grant!
  else
    insert into SYSTEM.audit_ddl_history(osuser, session_user, host, terminal, object_owner, obejct_type, object_name, sysevent,audit_date)
    values(
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','SESSION_USER') , 
      sys_context('USERENV','HOST') , 
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent,
    sysdate
    );
  end if;
end;
/

e.g.
SQL>
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

Ref: https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg14evt.htm