I need to know how to get the update information on a particular table with time stamp.
ie) Last - 1 ( I know the last update but i want to know previous update before this last update with Time stamp )
DB - 10g
just make a historical table with all timestamp with all the updates in the table.
Creating a trigger that when the table was updated copy the timestamp to this historical table, you can easily check the last -1.
Thanks for this,.. But all these shows the last update . i need Last -1, is there any query ...
to store it permanently with a record then you need to do what fran says with a trigger. Otherwise if youre using the
"select * from table as of timestamp" and youre not seeing the last -1 update, you need to go further back in time and hope your logs will stretch back that far. if they dont, and you really really need to see it, you're either mining logs for the update or you're restoring backups to test instances to get a point in time view of the data.
Only Auditing is the best way to the the info like :
audit insert, update, delete on username.tablename by access; and then
select username, timestamp, action_name, returncode
where obj_name = 'TABLE_NAME'