Re: user audit table clean up
From: Yechiel Adar <adar666_at_inter.net.il>
Date: Thu, 26 Feb 2009 07:53:03 +0200
Message-id: <49A62E3F.1030707_at_inter.net.il>
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;
Date: Thu, 26 Feb 2009 07:53:03 +0200
Message-id: <49A62E3F.1030707_at_inter.net.il>
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-lReceived on Wed Feb 25 2009 - 23:53:03 CST