Re: Audit object

From: Juan Cachito Reyes Pacheco <jreyes_at_dazasoftware.com>
Date: Tue, 18 May 2004 12:08:23 -0400
Message-ID: <002601c43cf2$5f0dd230$2501a8c0@dazasoftware.com>

HI, here is an example to save all data from ddls executed

The same to create and alter instead of drop

CREATE OR REPLACE TRIGGER sys.tgr_drop
 AFTER
  DROP <--
 ON DATABASE
DECLARE
 csql_text ora_name_list_t;
 n NUMBER;
 cSQL CLOB;

 var_user     varchar2(30);
 var_osuser   varchar2(30);
 var_machine  varchar2(64);
 var_process  varchar2(09);
 var_program  varchar2(48);
 var_sqltext  varchar2(1000);

BEGIN
 select s.username, s.osuser, s.machine, s.program  into var_user, var_osuser, var_machine, var_program  from sys.v_$session s

      ,sys.v_$sqlarea t
 where  s.audsid         = userenv('sessionid')

  and s.prev_sql_addr = t.address(+)
  and s.prev_hash_value = t.hash_value(+)   and username is not null;

 n:= ora_sql_txt(csql_text);
 FOR I IN 1..N LOOP
  cSQL:=cSQL||csql_text(i);
 END LOOP;  INSERT INTO SYS.ddls
 (dd2_user, dd2_time, dd2_objeto, dd2_tipo,DD2_SQL,   DD2_OSUSER , DD2_MACHINE , DD2_OPROGRAM )
  VALUES(
  'D-'||DBMS_STANDARD.LOGIN_USER,SYSDATE,   DBMS_STANDARD.DICTIONARY_OBJ_NAME,
  DBMS_STANDARD.dictionary_obj_type,cSQL,   var_osuser, var_machine, var_program   );
END;
/

Juan Carlos Reyes Pacheco
OCP
Database 9.2 Standard Edition

---

Please see the official ORACLE-L FAQ: http://www.orafaq.com

---

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

---

Received on Tue May 18 2004 - 11:09:06 CDT