Which process is inserting a record in a table

by Ravi
(Melbourne, Victoria,AUstralia)

Which process is inserting a record in a table, as I am getting some confusing results. Is there any way I can put a check on table property where I can see which process is trying to insert the record?


Probably the easiest way of finding out what is going on is to create a pl/sql trigger which fires for every insert, update and delete statement issued against the table and writes information about the process that is modifying the table to a log table via an autonomous transaction.

There are a couple of ways of determining the user and other details of the triggering process: you could query v$session for example but a better way would be to use the built-in function SYS_CONTEXT which takes 2 parameters - the context namespace value and the name of the attribute that you are interested in. See Oracle Database SQL Language Reference 11g Release 2 (11.2) for the exact syntax and the details.

You can define your own context namespaces and write code to populate it and query it (see Oracle Database Security Guide 11g Release 2 (11.2) for information about creating your own application context namespaces) but fortunately Oracle provide us with a standard namespace which already has most of the information that we would most likely want. This namespace is called USERENV and comes with a set of attributes whose values we can easily retrieve via the function call. The complete list of attributes is available in the Oracle documentation (see Oracle Database SQL Language Reference 11g Release 2 (11.2)) and some of them have to be populated programmatically, but you can extract information such as the current user, the operating system user and the sid without any extra coding.

Let's put this all together to see how it would work. We start with the creation of a log table with a free format column so that we can just dump whatever data we want to in there.

CREATE TABLE application_log (
log_date DATE,
log_text VARCHAR2(2000));


Next we need a trigger on our table:

CREATE OR REPLACE TRIGGER capture_modifier_info
BEFORE INSERT OR UPDATE OR DELETE ON my_table
DECLARE
  PRAGMA autonomous_transaction;
BEGIN
  INSERT INTO application_log VALUES
(SYSDATE, 'current db user: '|| sys_context('USERENV','CURRENT_USER')||
' sid:'||
sys_context('USERENV','SID')||
' os user:'||
sys_context('USERENV','OS_USER'));
  COMMIT;
END;


With this trigger in place, any process (or end-user via SQL*Plus or any other tool) that attempts to modify the table in any way will be logged in the application_log table which can be queried at any time. Note also that the trigger will write to the application log and commit the inserts independently of the main transaction due to the PRAGMA autonomous_transaction that we added to the trigger.

These Q & As are also about triggers
See our series of Oracle SQL tutorials and our series of PL/SQL tutorials for more help.

Click here for information about training courses both on-line and classroom-based.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.