Re: user audit table clean up

I have similar request and implemented it by:
truncate table old_audit;
insert into old_audit select * from audit; /* truncate needs lock and users log in all the time 
delete * from audit;
insert into audit (
select distinct user_id, max(login_time) over(partition by userid) from old_audit; 

Adar Yechiel
Rechovot, Israel



Xu, Roger wrote:

Hi List,

What is the best way for me to clean up the user audit table?
For each user, we only need to keep the last record.

USER_ID                        LOGON_TIM
------------------------------ ---------
TSDBA                          25-NOV-07
TSDBA                          26-NOV-07
...
TSDBA                          11-FEB-09
TSDBA                          25-FEB-09
TSDBA                          25-FEB-09
TSDBA                          25-FEB-09 (last record)

Thanks,

Roger Xu



--
http://www.freelists.org/webpage/oracle-l

Other related posts: