Re: track user's select statements

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 23 Nov 2006 16:40:26 GMT
Message-ID: <J96znJ.MDq@igsrsparc2.er.usgs.gov>

cobra wrote:

> 
> On 11月23日, 上午3时18分, DA Morgan <damor..._at_psoug.org> wrote:

>> There are a number of ways to accomplish this depending
>> on version including:
>>
>> Fine Grained Auditing
>> Streams
>> Change Data Capture
>> Log Miner
>> Triggers
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

> 
> thanks
> The version is Oracle 8.1.7.4,  Can Fine-Grained Auditing work?
> I think trigger is a good idea,but how to track select statements using
> trigger?
> I want to track the whole select statement when a user select some
> columns on a table.
> 

The old version of Oracle will limit what you can do. Since you are on Oracle 8.1.7, this precludes FGA, Streams, CDC from being used as those were introduced in later versions. So from the list above, that leaves you with Log Miner and Triggers. However, those are only good for tracking changes to the database and do not capture SELECT statements. In Oracle 8i, your only realistic option is to look towards the SQL Trace facility. For each user, have them turn on tracing:

ALTER SESSION SET sql_trace=true;

This will generate lots of trace files in your USER_DUMP_DEST directory, so make sure you have plenty of space.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Received on Thu Nov 23 2006 - 10:40:26 CST