I tend to agree with the argument "that's the way they wrote it". If I remember correctly, DDL always commits the current transaction in the session, even back in Oracle5 [and earlier ?] days. Most likely they couldn't write autonomous transactions then. That "autonomous transactions" have been available to us since 8i doesn't mean that autonomous transactions may not have been possible within the kernel earlier. I always think that the SCN mechanism is like a sequence and has been around since before Oracle6 but sequences were available to us only in Oracle6.
Hemant At 06:54 AM 23-01-03 -0800, you wrote:
Well, then even Tom's vague on this one.
"DDL locks are automatically placed against objects during a DDL operation
to protect them from changes by other sessions".
Well, then even an insert statement makes changes to DD objects. Ain't
locks taken then?
"DDL Starts by commiting".
Why not declare a savepoint, and later, rollback to that savepoint?
And theres another DBA friend, who speculates that the reason could be that
DDL statements always take an exclusive lock on the underlying object. If
that transaction is not ended implicitly, the locks would be waiting
forever until the user intervenes. This could have serious consequences on
the database, with a potential for deadlocks. Ahem. This by far, seems to
be the most convincing answer to me.
Ok. Maybe, thats just the way its coded. The point I have understood from
this thread, is that a commit is performed to protect the data dictionary.
And DML statements are not truly atomic, for while it maybe possible for an
DML statement to roll back, there are some changes that are indeed
committed.
Thanks
Raj
"Deshpande, Kirti"
<kirti.deshpande@ve To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
rizon.com> cc:
Sent by: Subject: RE: simple question on DDL
[EMAIL PROTECTED]
January 22, 2003
10:33 PM
Please respond to
ORACLE-L
Dan addressed this very well in his earlier post...
Here is what Tom Kyte says in his book (Expert one-on-one Oracle):
"DDL locks are automatically placed against objects during a DDL operation
to protect them from changes by other sessions".....
"DDL locks are held for the duration of the DDL statement, and are released
immediately afterwards. This is done, in effect, by always wrapping DDL
statements in implicit commits (or commit/rollback pair). It is for this
reason that DDL always commits in Oracle.".....
"So, DDL will always commit, even if it is unsuccessful. DDL starts by
committing - be aware of this. It commits first so that if it has to
rollback, it will not roll back your transaction. If you execute DDL, it'll
make permanent any outstanding work you have performed, even if the DDL is
not successful.".....
Refer to page 119...
- Kirti
-----Original Message-----
Sent: Wednesday, January 22, 2003 8:14 PM
To: Multiple recipients of list ORACLE-L
Dan,
If I may, essentially you are saying that changes to data dictionary tables
have to be committed immediately regardless of the outcome of the
transaction.
For instance in the following code, starting with an empty table t1
step 1: insert into table t1 values row1
step 2: create table t2
step 3: insert into table t1 values row2
step 4: rollback
At this point a select * from t1 will show only row1, since the ddl create
table t2 has inserted a commit. However, the point is, my transaction
should
have been from step 1 through step 4, not fromn step 3 through 4. The DDL
broke my txn at step 2 and another transaction started from there. The data
dictionary tables were updated and they should be committed; but that
commit
could have been done via an "autonomous transaction", not in the same
transaction the user issued.
The more I think about it, I see no point why a DDL should insert a commit.
This is different from saying that DDL itself may issue a commit to its
seprate transaction to update the catalog. Any thoughts on that?
Arup
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
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).
Hemant K Chitale My web site page is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
