John,
I agree w/ everything you said, except for the autocommit functionality.
Autocommit setting has no impact on whether DDL will commit or rollback
any in progress transaction. DDL always commits an in-progress
transaction. The short example below speaks for itself. (8.1.7.4 on
Solaris 2.8)
SQL> show autocommit
autocommit OFF
SQL> desc a
Name Null? Type
----------------------------------------- --------
----------------------------
COL1 NUMBER
COL2 NUMBER
SQL> select * from a where col1=-12345;
no rows selected
SQL> insert into a values(-12345,-12345);
1 row created.
SQL> create table xxx(a number);
Table created.
SQL> select * from a where col1=-12345;
COL1 COL2
---------- ----------
-12345 -12345
1 row selected.
Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and
a sense of humor was provided to console him for what he is." --Unknown
-----Original Message-----
Sent: Friday, January 23, 2004 12:10 PM
To: Multiple recipients of list ORACLE-L
A two-phase commit is simply a way to make sure that commits happen in a
distributed transaction the same way that they do in a local
transaction. The absolute rule is: "Everything commits or Nothing
does." In-between, with some parts committed and some not, is NOT
tolerable. So in your transaction, the change to the audit log is NOT
committed if any part of the transaction fails.
Everything from the beginning of a transaction up to a commit or
rollback command is part of the transaction. All DDL commands are
transactions unto themselves, so they end the prior transaction (which
is committed, if you have autocommit turned on, or rolled back
otherwise) and the command following a DDL command starts a new
transaction. Triggers execute within the same transaction as the
command that triggered them, and may not include a commit or rollback.
So any DML in a trigger is only committed if the entire transaction is
committed.
There is only one exception to this behavior. You can declare a stored
procedure as an Autonomous Transaction, which means that you are
starting a new transaction that is independant of the current
transaction. This means that the new transaction can commit or rollback
without affecting or being affected by the current transaction, and can
fail without causing the current transaction to fail or succeed, even if
the current transaction fails. This is very useful and powerful, but use
it with caution, because you are no longer protected by the normal
transaction safeguards.
-----Original Message-----
Sent: Friday, January 23, 2004 9:15 AM
To: Multiple recipients of list ORACLE-L
Hi All,
I have a before update trigger for a local table. I know Oracle does
not
commit the inserting audit entry into the audit log table until the user
commits the changes on the audited table. Can I assume Oracle issues
one
commit for both changes? When commit fails, both changes will be rolled
back. However, Oracle uses two-phase commit if a trigger updates remote
tables in a distributed database. What happens if Oracle commits the
change
in audit log table and my change subsequently fails?
_________________________________________________________________
Learn how to choose, serve, and enjoy wine at Wine @ MSN.
http://wine.msn.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: John Flack
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Bobak, Mark
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
