find which tables get updated..

[Agni]

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.

[Nige Ridd]

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.

Nige

[Agni]

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....

[Nige Ridd]

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.
Nige
( http://www.oracle-base.com/articles/10g/Flashback10g.php for more details )

[alit2002]

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

Alistair