Re: recover uncommited data

Comments embedded.
Satheesh Babu.S wrote:
> David,
> Recovering uncommitted data is not possible oracle, but if
> you want to do that at any *cost* there is an option by using this. To
> use it for one table or one schema or at db is left entirely with OP
> and level of overhead his db can bear. There is no free lunch.

Where did I say there was? My question still remains: how does this answer the OP's question? It doesn't, in actuality, it only proves you can offrer 'sotutions' which aren't truly viable. I would never suggest to anyone they use triggers and a duplicate schema to trap 'uncommitted data' simply to prove it can be 'recovered'. Uncommitted data is just that. If a transaction is killed in the midst of altering data, before any commit can be issued or before the data modifications are completed, that's too bad for the end user as that transaction or set of transactions need be re-run. 'Recovering' a partial transaciton makes no sense whatsoever, yet in some cases this is what you propose. I do not consider such 'recovery' of any help whatsoever. I consider that irresponsible.

> Using autonomous transaction to store the uncomitted data
> is general practice in auditing. Extending this option with little
> programming logic can help OP.

Auditing has a specific purpose; your 'extension' of this to preserve uncommitted data changes has no purpose other than to prove your academic prowess with respect to Oracle. It should not be used to make 'uncommitted data' persistent across instance failure or intentional rollbacks. That is simply asking for trouble.

> Any plsql developer can write a small
> routine to clear the data from mirror when rollback happens in main
> table.

And how will that code execution be triggered? No such 'on rollback' functionality exists in Oracle; I'm still curious how you'd remove this extraneous data from your 'mirror' table at the time of rollback, an act you apparently claim is possible. I offered at least two solutions to your 'double the data whether it's wanted or not' scenario, neither of which I would use nor would I recommend because your suggestion is an accident waiting to happen.

> If you think it is academic, sorry i can help it.

This whole exercise is academic. Yes, you CAN preserve 'uncommitted data' in a separate table. The issue is not whether it CAN be done, it's whether it SHOULD be done. And, no matter what your intent may be I find no valid reasons for doing so.

>
> Thanks and Regards,
> Satheesh Babu.S

David Fitzjarrell Received on Tue May 02 2006 - 07:51:29 CDT