Re: Few questions for experts

Comments and answers embedded

On Wed, 18 Apr 2001 14:42:50 +0100, "Jadranko Lucic" <jadranko.lucic_at_avl.com> wrote:

>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).

Yeah, this is one of the most recent nuisances DBA's are experiencing: developers not knowing anymore what a transaction is. At start of the transaction and at end of the transaction the database should be consistent, which evidently isn't the case when you commit every individual insert, update or delete.

>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?
No. When do they backup their database? Never? Online? Offline? In the latter case either the database will hang at shutdown or the transaction will be aborted.
>2. Does Oracle have something like timeout duration for transaction
>(connection)? How long transaction can take? Forever!?
 Yes, definitely
>3. What about rollback segment? How big it should be in this case?
 Depends on how many bytes change, so no guidelines can be provided
>4. Is there any other oracle DB parameters that have to be changed for this
>purpose?

 No
>5. How long oracle locks on table rows can be? Forever!?

Of course! This is robust software, not a toy.
>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?

just read up on this one in the Sql reference manual, online at http://technet.oracle.com, topics commit and rollback

>7. Is Rollback statement issued automatically if any oracle error appeared
>inside of transaction?

Statement level rollback, NOT transaction level rollback is applied automatically. So yes, you have explicitly to rollback

>8. Is there any way that rollback statement is issued without user
>interaction or knowledge (by Oracle DB itself under some condition)?

At shutdown, and when the database crashes and is rebooted, or if someone decides to kill the Oracle session
>
>Thanks for help

Hth,

Sybrand Bakker, Oracle DBA Received on Wed Apr 18 2001 - 09:38:59 CDT