Member Avatar for 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.

  • 3 Contributors
  • 4 Replies
  • 95 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by alit2002
Member Avatar for 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

Member Avatar for 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....

Member Avatar for 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 )

Member Avatar for alit2002

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

Alistair

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.