| Audit Select.. [message #520460] |
Mon, 22 August 2011 20:49  |
 |
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 #520466 is a reply to message #520465] |
Mon, 22 August 2011 21:34  |
|
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 #520478 is a reply to message #520466] |
Tue, 23 August 2011 01:01 |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
