x

How do you find out who has added a row to a table?

How can I find out who has added a row to a table?

more ▼

asked Nov 18, 2009 at 07:29 AM in Default

Ellie Ryder gravatar image

Ellie Ryder ♦
135 9 10 12

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

Using logminer:

  1. exec sys.dbms_logmnr.add_logfile('logfile which should contain the transaction that added the row'); (obviously substitute a real logfile name and path there... and you can add more than one logfile)
  2. exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG);
  3. Query table v$logmnr_contents for the information you need. Filter on SEG_NAME to see just the changes to that table, use columns SQL_REDO and SQL_UNDO to find the change you are looking for, and column USERNAME or SESSION_INFO to find the user who did the change.
  4. when you are done: exec sys.dbms_logmnr.end_logmnr;

Good luck :)

more ▼

answered Nov 19, 2009 at 12:16 AM

prodlife gravatar image

prodlife
201 1

Good point about looking in redo.
Nov 19, 2009 at 07:10 AM Andrew Mobbs

Toad has an interface to LogMiner if you're looking for help. Be careful with this package however, it's not that hard to corrupt your database if you don't know what you're doing.

Oracle's Auditing technology works great, but is pretty expensive to run. Most customers try it, then turn it off after they see what happens to performance.
Nov 19, 2009 at 04:40 PM HillbillyToad
(comments are locked)
10|1200 characters needed characters left

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

AUDIT INSERT ANY TABLE;

for all tables, or for the specific table,

AUDIT INSERT <tablename>;

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.

more ▼

answered Nov 18, 2009 at 03:11 PM

Andrew Mobbs gravatar image

Andrew Mobbs
1.5k 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x84
x17

asked: Nov 18, 2009 at 07:29 AM

Seen: 1862 times

Last Updated: Nov 18, 2009 at 07:29 AM