Re: Few questions for experts

Interesting enough (and not deprecating the prvious comments from Jonathon and Sybrand), there is a new capability in Oracle9i called DBWorkspace Management that is specifically provided to handle the concept of long running transactions.

This is sort of like version control - you open a workspace against a baseline of data - while in the workspace, the changes you make to the data are only seen by you (or other users that attach to the workspace). All changes via standard SQL etc. At the end you can discard the workspace (your changes are lost), or merge the changes back into the baseline of data - Oracle9i handles the merge and also does conflict resolution if the baseline has also changed in the meantime. And I believe the cost is only the deltas - i.e the data is not simply being copied from the baseline to the workspace, the workspace just contains the chaged data.

When I first heard of this my reaction was - Huh, why ? But one of the prime requirements is web site staging - i.e I have a current 'production' view of the data, which over a period of 24 hours is incrementally updated - perhaps a product catalog. At a certain time, I want the new changes to become 'production' as a single unit of work.

What-if model based analysis on data in a DW is also another idea that requires the concept of long running transactions in the relational model.

It may also become useful in the future in supporting online application upgrades and testing (but no promises at this stage) - you can upgrade the applications data and PL/SQL without closing the application down, perform your testing against the real data in a safe manner, and then flip the switch to move the new version into production.

BTW - I'm 99.999% sure 'workspaces' are maintained across shutdown and recovery in Oracle9i. I had thought that an advance of this technology was available on OTN as an early adopter download against Oracle8i 8.1.7, but can't find it now.

in article 9bk4tq$bpt$1_at_fstgss02.tu-graz.ac.at, Jadranko Lucic at jadranko.lucic_at_avl.com wrote on 4/18/01 6:42 AM:

> Hi,
> Current situation is that I have created multi-user client application that
> access to oracle via ADO (over OLEDB) using (so called) micro transaction
> (commit is issued after every DML statement).
> Now, I got a request to implement solution where transaction can let even
> several days. On the end of work application user would have possibility to
> choose either to save (commit) or to cancel (rollback) its work. Now I have
> some questions:
> 1. Did anyone have similar request and if so, how it is solved?
> 2. Does Oracle have something like timeout duration for transaction
> (connection)? How long transaction can take? Forever!?
> 3. What about rollback segment? How big it should be in this case?
> 4. Is there any other oracle DB parameters that have to be changed for this
> purpose?
> 5. How long oracle locks on table rows can be? Forever!?
> 6. How can I issue “Savepoint” from client side application or do a
> “Rollback” to some particular Savepoint also from client side application?
> Is it possible at all?
> 7. Is Rollback statement issued automatically if any oracle error appeared
> inside of transaction?
> 8. Is there any way that rollback statement is issued without user
> interaction or knowledge (by Oracle DB itself under some condition)?
>
> Thanks for help
>
>
> --
> _________________________
> dipl. ing. Jadranko Lucic
> AVL Zagreb
> tel:++385 1 6551117
> jadranko.lucic_at_avl.com
> _________________________
>
>
Received on Wed Apr 18 2001 - 14:40:29 CDT