How to track which table(s) has/have been modified

Question

I want to create a trigger that will show which table(s) has/have been modified and who (which users) changed the data on this table.

I found out the query below lists all tables that was modified in the last 30 days by ALTER statement but it didn't work out:

select schema_name(schema_id) as schema_name,
       name as table_name,
       create_date,
       modify_date
from sys.tables
where modify_date > DATEADD(DAY, -30, CURRENT_TIMESTAMP)
order by modify_date desc;

It showed error ORA-00942 Table or View not available.

This is the link with the similar question but for MySQL: enter link description here

@rjh @Ike Walker could you please help how to do the query using SQL Tools 1.8?

Should I use oracle Audit on the trigger to do this? I'm using SQL Tools 1.8 b38.

I have created a new table:

enter image description here

This is the query I used to make the table:

CREATE TABLE nag_Historic (
id NUMBER(10) NOT NULL,
tst_date DATE DEFAULT SYSDATE NOT NULL,
Ändere VARCHAR2(20) DEFAULT USER NOT NULL,
title VARCHAR2(100) NOT NULL
);

ALTER TABLE nag_Historic
ADD (
CONSTRAINT nag_pk PRIMARY KEY (id)
);

CREATE SEQUENCE nag_id START WITH 1 INCREMENT BY 1;

I have also made the trigger:

CREATE OR REPLACE TRIGGER trg_nag_histry
BEFORE INSERT OR UPDATE OR DELETE ON nag_historic
FOR EACH ROW
BEGIN
SELECT nag_ID.nextval
INTO :new.id
FROM dual;
END;

This is how I inserted the value:

INSERT INTO nag_Historic (title)
VALUES ('johan');

I have figured out this query for the audit:

SELECT * FROM DBA_STMT_AUDIT_OPTS;

SELECT * FROM dba_priv_audit_opts;

But that still doesn't show which tables have been modified (changed). Please help me, I'm totally stuck.

Check this https://oracle-base.com/articles/8i/auditing .. you need enable it — Sujitmohanty30, Sep 03 '20 at 06:36
I can not find my 'init.ora' How can I enable my Audit in SQL? Is there a way to activate it using query in SQL tool? I have also tried to run several queries: "AUDIT ALL BY SO BY ACCESS; audit update table, delete table, insert table by SO by access; audit execute procedure by SO by access;" it only said "audit, executed in 8 ms" but didn't give any results. — ella widya, Sep 03 '20 at 06:49
@Sujitmohanty30 back again to my original question. I want to create a trigger that will show which table(s) has/have been modified and who (which users) changed the data on this table. Is my approach correct that I need to use oracle SQL audit for this? — ella widya, Sep 03 '20 at 07:02
@ellawidya Using Oracle AUDIT is quite a large undertaking in terms of managing the tonnes of additonal diskspace that gets used, and query this for specific information can be resource hungry for large volumes I've found. To use this will require DBA input and set up. Rolling your own auditing via TRIGGERS can be easier to implement and control. — TenG, Sep 03 '20 at 15:49
@TenG - I strongly object. "rolling your own auditing via TRIGGERS " is simply re-inventing the wheel that Oracle has already done. And guaranteed to be less efficient. It is not resource hungry for large volumes if you implement proper housekeeping, just like you'd need to do with a home-grown audit table. Of course it requires coordination withe DBA. But that's what the DBA's job is. Developers and DBAs should be working hand-in-glove with each other. — EdStevens, Sep 03 '20 at 16:01

score 0 · Answer 1 · answered Sep 03 '20 at 15:57

No, you don't want to create a trigger. Everything you are trying to capture there is already done by properly configured AUDIT. In your linked tutorial, the reference to init.ora really should be to init.ora. So, if your database is named 'fred', your init file should be $ORACLE_HOME/dbs/initfred.ora. (Or, if windows %ORACLE_HOME%\database\initfred.ora). But actually you should not be using init.ora at all, you should be using the spfile ($ORACLE_HOME/dbs/spfilefred.ora). Note that is a binary file and must not be edited with a text editor. You modify parameters with 'alter system set ....'

The audit commands you issued did not return your expected results because that is not what they are for. The simply direct the database to begin auditing what was specifed. They do not report anything. And any select from dba_*_audit_opts only reports what is being audited - what audit rules are in place.

To actually get an audit report of audited activity, you have to query DBA_AUDIT_TRAIL.

And auditing is not retroactive. The audit trail will have no record of anything prior to the time you enabled an auditing rule.

How to track which table(s) has/have been modified

1 Answers1