How can I find out who has added a row to a table?
asked Nov 18, 2009 at 07:29 AM in Default
Ellie Ryder ♦
Actually, it is recorded by default - just not in an easy to access location...
If you have the redo logs from the time the row was added to the table, you can use logminer to find our who did it. It is not something you should do all the time, just if you find yourself with a row that you really need to know where it came from (if you need it all the time, audit or triggers will be the best solution).
Good luck :)
answered Nov 19, 2009 at 12:16 AM
That information isn't recorded by default. You need to have enabled some sort of auditing before the row has been inserted. Oracle has several options for auditing, depending on your requirements. See the Concepts manual (http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/security.htm#sthref2916) for an overview of the various options of auditing in Oracle, and the Security manual (http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/auditing.htm#DBSEG125) for more details.
For your specific question, auditing inserted rows could be enabled with
for all tables, or for the specific table,
The results would then be viewable through the DBA_AUDIT_TRAIL view
However, if it's already after the event and you didn't have auditing, you could try to use Flashback queries to determine the transaction that made the change, which will allow you to find out what else that transaction changed, and possibly some associated metadata, which could help track down the culprit. That obviously depends on running the Flashback queries soon after the event, while the UNDO is still stored.
answered Nov 18, 2009 at 03:11 PM