Oracle Database Discussions

The CT has the following issue, we just need only to provide a workaround:

"We have a separate security group that is scanning our audit trail at 30
second intervals looking for signs of a denial of service attack (or something)
but their constant queries are form of denial of service themselves.

Under 9i we put an index on SYS.AUD$.TIMESTAMP# and that minimized their impact.
Under 10g we found that SYS.AUD$.TIMESTAMP# was all null and that SYS.AUD$.
NTIMESTAMP# is used instead. However putting a straight index on NTIMESTAMP# doe
s not lead their query on DBA_AUDIT_TRAIL to use the index.

In 9i our strategy was to allow AUD$ to cover a whole month and then at the
beginning of each month we would copy the old month's data to another table,
archive it (ie preserve it offline for at least 2 years) to meet corporate
audit requirements. If we cannot get a working index for this table we'll have
to look at doing this on a nightly basis and this will lead to other problems."

checked the dba_audit_trail view in 10g:

timestamp column is defined as:
cast ( /* TIMESTAMP */
(from_tz(ntimestamp#,'00:00') at local) as date),

a regular index on column ntimestamp# would indeed not help!
Tried to define a function index :

SQL> create index test_aud$_idx on aud$(cast ((from_tz(ntimestamp#,'00:00')
at local) as date));
create index test_aud$_idx on aud$(cast ((from_tz(ntimestamp#,'00:00') at
local) as date))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed



create index test_aud$_idx on aud$(from_tz(ntimestamp#,'00:00') at local);
SQL> create index test_aud$_idx on aud$(from_tz(ntimestamp#,'00:00') at
local);
create index test_aud$_idx on aud$(from_tz(ntimestamp#,'00:00') at local)
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed

Please advise.
AIM:Mhamed EG
EMAIL:mohamed.hamed@oracle.com