Oracle 11.2 auditing perf
From: Leroy Kemnitz <lkemnitz_at_uwsa.edu>
Date: Thu, 15 Oct 2015 14:47:31 +0000
Message-ID: <BN3PR0701MB16374F08675A9113A3DA639DB63E0_at_BN3PR0701MB1637.namprd07.prod.outlook.com>
All -
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count, TO_CHAR(MIN(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time, TO_CHAR(MAX(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time FROM sys.dba_audit_session
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00');
LeRoy Kemnitz
Oracle Database Administrator
UW System Administration
Phone: (608) 265 -5775
Fax: (608) 265 - 2090
Date: Thu, 15 Oct 2015 14:47:31 +0000
Message-ID: <BN3PR0701MB16374F08675A9113A3DA639DB63E0_at_BN3PR0701MB1637.namprd07.prod.outlook.com>
All -
What are people doing to improve the performance of the sys.aud$ table in Oracle 11.2?? Custom indexing??
I am purging records daily from the audit and only keeping what is required. I also moved the table out of the system tablespace and into an audit tablespace.
This sql is still running extremely slow for me -
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count, TO_CHAR(MIN(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS first_occur_time, TO_CHAR(MAX(timestamp), 'yyyy-mm-dd hh24:mi:ss') AS last_occur_time FROM sys.dba_audit_session
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00');
LeRoy
LeRoy Kemnitz
Oracle Database Administrator
UW System Administration
Phone: (608) 265 -5775
Fax: (608) 265 - 2090
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 15 2015 - 16:47:31 CEST