-
Oracle people:
Could someone give me a hand and show me a script that shows who has made changes(new table entries or other changes) to the DB without having to startup the whole "audit" system ?
I've tried a script from my not so handy oracle book- doesn't work and I've failed to hack it into shape :-(
Here it is:
-----
col Owner format a20
col Object_Name format a30
col Timestamp format a20
select Owner, Object_Name, Object_Type, Status, Timestamp
from DBA_OBJECTS
where SUBSTR(Timestamp,1,10) = TO_CHAR(sysdate-1,'YYYY-MM-DD')
order by Owner, Object_Name
/
------
Thanks !
Roger
-
Oracle People:
Sometimes it's good not to get an answer :-) as I've been forced to dig a bit deeper into Oracle.
I can't use logminer and I can't use auditing so I've created the following trigger as a sort of low budget auditing:
TRIGGER STYLE AUDITS FOR ORACLE
First create a table that will contain the audit information:
SQL> create table amy.aud (
ID varchar2(20),
TIME varchar2(20),
TERM varchar2(20),
TYPE varchar2(20));
Now create a trigger script:
----------------------------------------
create or replace trigger trig1
after insert or update on AMY.artist
for each row
declare
time_now DATE;
terminal CHAR(10);
begin
time_now := SYSDATE;
terminal := USERENV('TERMINAL');
IF INSERTING THEN
INSERT INTO amy.aud
VALUES (user, time_now,
terminal, 'INSERT');
ELSIF DELETING THEN
INSERT INTO amy.aud
VALUES (user, time_now,
terminal, 'DELETE');
ELSE
INSERT INTO amy.aud
VALUES (user, time_now,
terminal, 'UPDATE');
END IF;
END;
/
---------------------------------------------------
