Home » RDBMS Server » Server Utilities » Audit Select.. (Oracle 10g)
Audit Select.. [message #520460] Mon, 22 August 2011 20:49 Go to next message
AjitSharma
Messages: 5
Registered: August 2011
Junior Member
Hi,
I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.
We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log.

Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.

Regards,
Ajit
Re: Audit Select.. [message #520462 is a reply to message #520460] Mon, 22 August 2011 20:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

>I am importing some data from Oracle into another database on a regular basis.
EXACTLY what is being done as described in line above?
Re: Audit Select.. [message #520464 is a reply to message #520462] Mon, 22 August 2011 21:03 Go to previous messageGo to next message
AjitSharma
Messages: 5
Registered: August 2011
Junior Member
>It would be helpful if you followed Posting Guidelines -
I am not sure which part of it i missed.

>EXACTLY what is being done as described in line above?
I am running few SELECT queries on oracle database to get some data using C# code.

Re: Audit Select.. [message #520465 is a reply to message #520464] Mon, 22 August 2011 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am not sure which part of it i missed.
#4, #5, #7, #8

>I am running few SELECT queries on oracle database to get some data using C# code.
One alternative is to re-run queries as SELECT COUNT(*)
Re: Audit Select.. [message #520466 is a reply to message #520465] Mon, 22 August 2011 21:34 Go to previous messageGo to next message
AjitSharma
Messages: 5
Registered: August 2011
Junior Member
Issue
>One alternative is to re-run queries as SELECT COUNT(*)
I cannot re-run any of the queries as SELECT COUNT(*) as the data in the underlying table changes very frequently and i may not receive the correct result. When i rerun the queries they do work. I run my queries on views.
The issue is the queries do not return any data randomly whereas they should. So i wanted a way to track the same in Audit log.


Guidelines
Sorry, for not following the guidelines. As this is the first time and i was not sure, if these values hold any relevance to the query i have.
#4
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

#5
Red Hat Enterprise Linux Server release 5.6 (Tikanga)
x86_64 arch

#7, #8
The reason i didn't put any code as they do not hold any relevance to the query.

I am not sure how to display that i put in effort to resolve the same, but i can definitly write my query.

SELECT b.* 
FROM   (SELECT Row_number () over (PARTITION BY a.customer_ref_id, a.TIMESTAMP 
               ORDER BY 
               a.totaleventcount DESC) AS snum, 
               a.* 
        FROM   (SELECT SUM (eventcount) AS totaleventcount, 
                       TIMESTAMP, 
                       customer_ref_id, 
                       attackeraddress, 
                       direction 
                FROM   xx.customviewname 
                WHERE  TIMESTAMP > Cast (( From_tz ( 
                                         ( To_timestamp ( 
                                           '2011-02-20 00:00:00', 
                                                   'YYYY-MM-DD HH24:MI:SS') 
                                                             ), 
                                           'Australia/Sydney') AT 
                                           TIME ZONE 
                                                   'Etc/GMT' ) 
                                                             AS TIMESTAMP (3)) 
                       AND TIMESTAMP <= Cast (( 
                           From_tz (( To_timestamp ( 
                                      '2011-02-21 00:00:00' 
                                      , 
                                      'YYYY-MM-DD HH24:MI:SS') 
                                                     ), 
                           'Australia/Sydney') AT 
                           TIME 
                           ZONE 
                               'Etc/GMT' ) AS 
                                          TIMESTAMP (3)) 
                       AND TIMESTAMP IS NOT NULL 
                       AND eventcount IS NOT NULL 
                       AND customer_ref_id IS NOT NULL 
                       AND deviceaddress IS NOT NULL 
                       AND direction IS NOT NULL 
                GROUP  BY customer_ref_id, 
                          TIMESTAMP, 
                          attackeraddress, 
                          direction) a) b 
WHERE  b.snum < 25 
ORDER  BY b.snum, 
          b.customer_ref_id, 
          b.TIMESTAMP  

And the queries i use to check out the audit log is
SELECT * 
  FROM   sys.aud$ 
  WHERE  userid = 'EXX' 
  ORDER  BY ROWID DESC;

[Updated on: Mon, 22 August 2011 21:43]

Report message to a moderator

Re: Audit Select.. [message #520469 is a reply to message #520466] Mon, 22 August 2011 22:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>The issue is the queries do not return any data randomly whereas they should. So i wanted a way to track the same in Audit log.
upon what do you conclude that "the queries do not return any data"?
In my experience, SELECT is more reliable that homebrew C# code.
Re: Audit Select.. [message #520470 is a reply to message #520469] Mon, 22 August 2011 22:28 Go to previous messageGo to next message
AjitSharma
Messages: 5
Registered: August 2011
Junior Member
I think i am not able to put it right words.

C# is only used to send "Select" queries to retrive data. The "Select" queries do not return any data sometimes but appear correctly in the Audit Log.

Question
Is it possible to configure the Audit Log to retrieve how many rows were returned by Select query.

If yes, how?


Re: Audit Select.. [message #520478 is a reply to message #520466] Tue, 23 August 2011 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT * 
  FROM   sys.aud$ 
  WHERE  userid = 'EXX' 
  ORDER  BY ROWID DESC;

Your ORDER BY clause is useless and just slows down your query.
Also use DBA_AUDIT_STATEMENT instead of sys.aud$.

Quote:
Is it possible to configure the Audit Log to retrieve how many rows were returned by Select query.

No (whatever the Oracle audit you choose and didn't tell us).

Regards
Michel

[Updated on: Tue, 23 August 2011 01:01]

Report message to a moderator

Re: Audit Select.. [message #520481 is a reply to message #520478] Tue, 23 August 2011 01:10 Go to previous message
AjitSharma
Messages: 5
Registered: August 2011
Junior Member
Thanks for the reply.

btw- I am using Audit = db,extended.
Previous Topic: problem using sqlldr(2 Merged)
Next Topic: SQL Loader Number Format
Goto Forum:
  


Current Time: Thu Mar 28 04:02:23 CDT 2024