Re: Auditing an app's SQL - How?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 08 Dec 2004 09:13:36 +1100
Message-ID: <41b62b0f$0$26811$afc38c87@news.optusnet.com.au>

Walt wrote:
> Howard J. Rogers wrote:
>

>> Of course, every normal insert will generate Redo. So make friends 
>> with Log Miner, and you'll be able to see the redo your insert 
>> statements are generating.

>
>
> Yeah, if it's successful. In my case the problem is that the insert
> statement is failing and throwing an error.* I'd like to take a look at
> the SQL that's causing the trouble. If it fails, it won't make it to
> the redo logs, right?
>
> Guess I should have said that to begin with...I'm trying to capture SQL
> statements that *fail*.

I guess you should have. And I guess you should still take a look in V$SQL_PLAN, V$SQLAREA, V$QLTEXT (and those are the correct names this time, because I was able to check them).

In a little test I just did, for example, I did this:

update emp set sal=900 where ename='HOWARD'; 0 rows processed
select sql_text, hash_value from v$sqlarea where sql_text like '%HOWARD%';

[which gave me the hash value, so on 9i I could then do...]

select * from v$sql_plan where hash_value = 47674389847

...at which point you can see the execution plan the statement took, even though it ended up updating zero records.

If the statement simply fails because of syntax errors, though, forget it... because it doesn't even get parsed. For example, if I log on as SYS and do 'update emp set sal=900.... etc etc', then I get a 'table or view doesn't exist' error message, and hence no parsing takes place.

Regards
HJR Received on Tue Dec 07 2004 - 16:13:36 CST