Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DML tracking in Oracle

Posted on 2014-03-21
8
Medium Priority
?
604 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 2000 total points
ID: 39945047
don't use a trigger, use auditing

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

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

604 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