Solved

DML tracking in Oracle

Posted on 2014-03-21
8
562 Views
Last Modified: 2014-03-25
Hello Experts,

I need help on tracking any DML operation done on my schema.

For example if I have few tables on schema "ABC":

Now any insert, update , delete queries executed on my schema should be captured.

I have a table called :

-- Created to track the DML openration done on one schema
CREATE TABLE ms_sra_dml_log (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
object_type VARCHAR2(30),
ATTEMPT_BY varchar2(30),
ATTEMPT_DT date,
SERVER_TIME timestamp (6) with time zone, 
SESSION_TIME TIMESTAMP (6) WITH TIME ZONE,
sql_text varchar2(4000)
);

Open in new window


Now I want to create one trigger which should capture any changes on those tables.
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39945047
don't use a trigger, use auditing

that's what it's there for
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39945072
don't use a trigger, use auditing

that's what it's there for

it depends, but in general: yes

if not, then you'd have to write (at least) one (compound) trigger for each table that should be monitored....
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39945650
I want to track is like if I start from today I want to track one schema and want to track each  insert,update, delete statement that was executed .

Wanted to get the insert/delete/update script that was executed.

I cannot create triggers on each table.
So need some help to get this done.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39945864
alter system set audit_trail=db,extended scope=spfile;  -- If it's not already on you'll need this

AUDIT SELECT, INSERT,UPDATE,DELETE ON your_schema.table1;
AUDIT SELECT, INSERT,UPDATE,DELETE ON your_schema.table2;
AUDIT SELECT, INSERT,UPDATE,DELETE ON your_schema.table3;
AUDIT SELECT, INSERT,UPDATE,DELETE ON your_schema.table4;



the database will need to be restarted before the audit_trail change takes effect


use auditing because  a dml trigger can't capture selects

if you don't need selects, simply remove them from the list of audited options
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39948804
Can audit_trail be implemented to all the tables that are exists within a schema ?
I don't want to select the tables individually.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39948885
Yes, auditing is easy to turn on, but in my experience it captures a lot of information that i don't care about, and it doesn't capture the actual SQL command that was used.

Writing a separate trigger for each table seems like more work, at least initially, but it may actually be less work overall and it may generate a *LOT* smaller volume of data that needs to be stored and managed.

No, Oracle does not support us writing a single, generic trigger that will apply to multiple tables.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39949131
>>> , and it doesn't capture the actual SQL command that was used.

extended auditing will
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39953020
thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question