RE: What do you use autonomous transactions for?
- From: "Tony Davis" <tony@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 17 Feb 2004 02:03:57 -0800
Hi,
Apparently, it is not recommended to use autonomous transactions to avoid
mutating table errors, as documented in Metalink Note:65961.1:
"As all database changes are part of a transaction, if a parent has modified
data, but not committed it at the point the autonomous transaction begins, then
those modifications are not visible to the child...It is important to remember
that if a trigger is running as an autonomous transaction, although it still
has access to :OLD and :NEW values as appropriate, it does not see any rows
inserted into the table by the calling transaction. Thus, using an autonomous
trigger to obtain a maximum value currently in the table...[in other words, a
mutating table scenario]...is unlikely to work."
Cheers,
Tony.
-----Original Message-----
From: Stefan Jahnke [mailto:Stefan.Jahnke@xxxxxx]
Sent: 17 February 2004 09:21
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: AW: What do you use autonomous transactions for?
Hi
That's a good idea. I think the only drawback here is:
What if part of your work done within the trigger is
ok, before you encounter a problem. You can't rollback
what you've done in your AT then. You would have to do
a "roll-your-own"-rollback by keeping track of what you
changed and issuing a second AT to undo your work.
That might be a real performance problem, depending of
how often your triggers are executed and what kind of
work they're supposed to do.
Stefan
-----Ursprüngliche Nachricht-----
Von: David.Schmoldt@xxxxxxxxxxxxxxxxxxxxxxxxx
[mailto:David.Schmoldt@xxxxxxxxxxxxxxxxxxxxxxxxx]
Gesendet: Dienstag, 17. Februar 2004 00:29
An: oracle-l@xxxxxxxxxxxxx
Betreff: RE: What do you use autonomous transactions for?
I *think* I read you can use autonomous transactions in table triggers =
to
avoid some mutating table situations. Haven't tried it myself.
I do use them a lot in logging the progress of long-running jobs, =
logging
errors before rollback, etc.
Dave
> -----Original Message-----
> From: Powell, Mark D [mailto:mark.powell@xxxxxxx]
> Sent: Monday, February 16, 2004 1:29 PM
> To: 'oracle-l@xxxxxxxxxxxxx'
> Subject: RE: What do you use autonomous transactions for?
>
>
> I would think that error message logging, debugging, and
> auditing are the
> primary uses of user created anonymous transactions.
>
>
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ryan
> Sent: Monday, February 16, 2004 2:21 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: What do you use autonomous transactions for?
>
>
> The only thing I have used them for is logging dbms_jobs to
> tables. I do not
> want the transaction to commit, but I want to track my progress.
> What have you used them for? Anything interesting?
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
