Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to trace the activity on a single table in oracle

Posted on 2010-11-17
11
Medium Priority
?
2,674 Views
Last Modified: 2012-05-10
I would like to trace all the activity (Selects, updates,inserts, deletes) on a single table in oracle. Is it possible if so How do you configuure it.  The trace needs to be on a single table. It can not be a large scale trace as the server has a high disk and CPU load now.

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
 
0
Comment
Question by:fpkeegan
[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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34157793
For a single table I might go with a trigger.

The other way is oracle auditing:
http://download.oracle.com/docs/cd/B10500_01/server.920/a96521/audit.htm
0
 
LVL 35

Expert Comment

by:johnsone
ID: 34157843
You would not be able to audit select with a trigger.  If you need to audit select, then you need to take the hit of Oracle auditing.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34157857
>>You would not be able to audit select with a trigger.

Excellent point!
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 35

Expert Comment

by:johnsone
ID: 34157879
Just wanted to point that out.  If select is not an issue, then a trigger is the way to go.  Especially with one table.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34157913
Apart from AUDIT you should take a look at FGA (Fine Grained Auditing): http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_fga.htm#i1002216

It can audit SELECTS and everything else. The big plus over plain AUDIT is that you can have much more control over who gets audited, what exact SQLs get audited, etc. You can even choose to audit operations only when they affect the specific columns you care about.
0
 
LVL 11

Accepted Solution

by:
Akenathon earned 2000 total points
ID: 34158003
@johnsone: AUDIT and triggers are very different. Even if we do not consider the fact that triggers don't work with SELECTs, AUDIT is faster because it's hardcoded into the Oracle executable.

Triggers need someone to code and mantain them, and could be disabled (which can only be detected by using AUDIT anyway), so they are not as "secure". On the other hand, they provide much better control over what gets traced at the expense of PL/SQL execution. Note that even if you code a trigger which does nothing, you still have the overhead of switching between SQL and PL/SQL, which will be noticeable in a bulk operation if the trigger is FOR EACH ROW. And that's another difference: AUDIT won't let you do anything FOR EACH ROW. You only get to choose BY ACCESS (much the same as a trigger which is not FOR EACH ROW) or BY SESSION (an option the triggers don't have, which can further improve AUDIT performance over triggers if you are OK with session granularity).
0
 

Author Comment

by:fpkeegan
ID: 34158045
What is the set up for fine grain auditing on a single table. All I want to look at is the statements used agains the table Select, update, inserts, delete.  Does is work on oracle 9.2. The link is for 10.2.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34158128
FGA was introduced in 9i:

http://www.oracle.com/technetwork/database/security/index-083815.html

That link also has an Oracle-By-Example link (although for 10g but should be similar if not the same).

The only thing I cannot locate is if FGA is part of Advanced Security or free to use with a standard license.
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34158146
Yes, it does work for 9i: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_fga2.htm#1002217

Here are some examples to get you going:

http://www.oracle-base.com/articles/9i/SecurityEnhancements9i.php#FineGrainedAuditing

http://www.hawkeyetechnology.com/DATABASE%20AUDITING.pdf (look around slide 35)

And... if you need to capture the SQL statement, FGA is your only option. AUDIT won't do it. The trigger won't get fired for queries, so you NEED to understand and use FGA :-)
0
 
LVL 35

Expert Comment

by:johnsone
ID: 34158342
Understood, but what are they trying to accomplish with the audit records?  Can even fine grained auditing tell me who changed a specific record, when they changed it and what the history of changes to a record are?

Auditing and fine grained auditing have their own overhead associated with them.  When I have auditing turned on, Oracle has to decide whether the action that just occurred requires an audit record to be written.  That is not free.  On the other hand a trigger knows when it is supposed to do its job.  When you are talking about 1 table out of possibly thousands, is it worth the overhead of auditing for just one table?  Maybe yes, maybe no.

Triggers do require someone to write them, however these type of auditing triggers are fairly simple and if done correctly require almost no maintenance.  Yes, it could be disabled, however your orgainzation should have the controls in place to prevent that type of thing from happening.  One can also argue that auditing records could be deleted and that presents just as much of a security hole as disabling a trigger.

0
 
LVL 11

Expert Comment

by:Akenathon
ID: 34158690
@johnsone: Yes, FGA can tell you who did what and when, and what was the SQL. If the author had asked for a history, still I would have to recommend FGA because what he did say is that he needed SELECTs audited (FGA can run a PL/SQL block of your choice just like a trigger, so whatever a trigger does, FGA also can do).

AUDIT is not an overhead if the AUDITed operations are never executed. You are probably already paying for the negligible overhead of having audit_trail != NONE (if your data is any worth and you have audit_trail=NONE you are not even auditing connections, so you have much bigger problems than the "IF" overhead).

I obviously agree that you can cover your tracks if you have enough privileges... AUDIT and FGA are designed with security in mind so they are a bit harder to circumvent, but of course it can be done.

Please note: I'm not saying this to argue that one solution is intrinsecally "better" than the other... I do use triggers for auditing purposes, I just see that they are not even an option for the author's problem at hand (because of the queries), but all these alternatives and some others not even mentioned have their place in Oracle's security/forensics.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

661 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