Oracle: How to identify data and schema changes

Question

I have a requirement to gather what are the database data changes or schema changes occurred after executing a nightly batch. For example there is a table employee which has two records. After nightly batch suppose one record inserted and one record updated. I want to capture what record is updated and what record is inserted. I am using Oracle database. I am looking for a script to do this as we have some issues to get licenses for new tools that does this task. So anyone can advise how this can be done programatically or using Oracle 11g built in functions? Any sample code is greatly appreciated.As we have large number of tables, I am looking for a generic way to do this.

Thanks

Oracle has built-in auditing capabilities. Read the documentation. — OldProgrammer, Dec 02 '15 at 23:10

score 3 · Answer 1 · edited May 23 '17 at 11:44

3

I would suggest using triggers on the changes you want to capture and inserting that information into another table that captures those changes. There's some info right here in stackoverflow the best way to track data changes in oracle

If triggers are not a viable option, look into INSERTing into 2 tables at once, one being your target table and one being you logging/change capture table. Here is an example on stackoverflow

Oracle INSERT into two tables in one query

A third option would be table auditing. See the following on stackoverflow

Auditing in Oracle

edited May 23 '17 at 11:44

Community

1
1

answered Dec 02 '15 at 23:07

tale852150

1,618
3
17
23

Over using triggers may impact Databse badly. – Avrajit Roy Dec 03 '15 at 07:42
I cannot go for triggers as mine is a batch job that processes large data and having a trigger would degrade the performance. – user1019072 Dec 03 '15 at 15:25

score 0 · Answer 2 · answered Dec 03 '15 at 03:53

0

In OLTP systems, you can add audit columns in the table create_date, update_date or last_modified_time, transaction_type.

With create_date, update_date - you can set default sysdate to create_date and then you need to modify application logic to update update_date. Trigger also will work, instead of changing code at the small cost of performance.

With last_modified_time, transaction_type - you need to update those 2 fields on insert or update as part of your application logic or using trigger.

answered Dec 03 '15 at 03:53

Durga Viswanath Gadiraju

3,896
2
14
21

What about tracking deletions? – APC Dec 03 '15 at 22:42
For that you need to have log table and trigger to insert into log tables when ever there is delete. – Durga Viswanath Gadiraju Dec 03 '15 at 22:45
Don't tell me. Edit your answer .) – APC Dec 03 '15 at 22:45
User have not asked that, he just want to know what records are inserted or updated :) – Durga Viswanath Gadiraju Dec 03 '15 at 22:47

Oracle: How to identify data and schema changes

2 Answers2