How to compare data in table (before and after an operation)?

Question

Is there any free tool or a way to get to know what has changed in database's table?

Answer 1

You could take a copy before the update

CREATE TABLE t2 AS SELECT * FROM t1

Run your update

Then to show the differences

use this to show updates:

SELECT * FROM t1
MINUS
SELECT * FROM t2

use this to show the deletes:

SELECT * FROM t2
WHERE NOT EXISTS(SELECT 1 FROM t1 WHERE t1.primary_key = t2.primary_key)

and finally this to check the total number of records are identical

SELECT count(*) FROM t1

SELECT count(*) FROM t2

Note: If there are other sessions updating t1 it could be tricky spotting your updates.

Answer 2

Triggers really should be avoided but ...

If you are in a non-production environment you can set up a trigger to perform logging to a new table. You need 5 fields something like this:

LogTime DateTime;
Table   Varchar2(50); -- Table Name
Action  Char;         -- Insert, Update or Delete
OldRec  Blob;         -- Concatenate all your field Values
NewRec  Blob;         -- Ditto

The Beauty of this is that you can select all the OldRecs and NewRecs for a given timespan into text files. A comparison tool will assist by highlighting your changes for you.

Any help ?