Can I selectively delete from sys.aud$ table ???
Alan Rosenthal, February 17, 2004 - 7:15 pm UTC
Hi,
I want to do the samething. However, my aud$ table is growing very fast and I only care about who logs in - not how many times they log in. Therefore, I would like to purge this table.
Is it safe, sane and kosher to delete all records for each userid except for the one with the max sessionid (I just want to keep one record for each user and max sessionid seemed like a good enough way to pick the survivors):
-- create a list of what I want to keep.
create table aud_list as
select userid, max( sessionid ) sessionid
from sys.aud$
group by userid ;
-- Delete everything not in the list.
delete from sys.aud$
where sessionid not in
select sessionid
from aud_index ;
-- get rid of list.
drop table aud_list ;
Thanks,
Alan
February 17, 2004 - 7:39 pm UTC
what version do you have?
Version
alan rosenthal, February 18, 2004 - 1:07 pm UTC
Hi,
Sorry I forgot to include the versions. One DB is 9.2.0.4 and the other one is 8.1.7.3
February 18, 2004 - 9:29 pm UTC
use a logon trigger instead. just update a row by user name and if sql%rowcount = 0, insert it.
(eg: don't use auditing in this particular case)
Another similar article
eric givler, February 18, 2004 - 9:52 pm UTC