Hi Guys...

i'm not so good at oracle, sql and related stuff.. but i do need to write some simple query's from time to time and i do need to understand that which all tables get updated by a particular transaction. I can do that by going through the code, but problem is i'm damn lazy and also that usually i like to automate most of this donkey work. would be great if any of you oracle guru's could tell me that is there someway i can find that which all tables got updated by a transaction. i expect some system view or some system table to contain the states of the tables(last modified) or something like that which i can query before and after i run a transaction or something on those lines.

10 Years
Discussion Span
Last Post by alit2002

You could always write some PL/SQL which adds a trigger to each table, as the table gets changed you could produce some sort of log.



hmm.. but what if i dont want to modify the db? .. isn't there a system view which has the state of the tables? .. adding a trigger on each table will make the system too slow, even for a development team....


Don't know of any view, but to not alter the database you could get the last SCN and for each table output whats changed since then.
You can use:
SELECT current_scn, SYSTIMESTAMP FROM gv$database;
to get the current SCN and then for each table:
SELECT * FROM tablea AS OF SCN nnnnn;
to see if any changes have been made.
( http://www.oracle-base.com/articles/10g/Flashback10g.php for more details )


Have you thought about using Log Miner to view the transactions? It may give you what you need.


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.