My point wasn't to pick any particular detail
of any particular example . I was merely making
the point that whilst the concept of DDL without
commits seems to be straightforward, the requirement
for designing something that could analyse and handle
all the consequent errors that might be a
Take your first example :
insert into t1 values (1);
drop table t1;
-- how to deal with self-deadlock ?
insert into t1 values (2);
commit;
Why does Oracle HAVE to commit when the DROP TABLE is issued ?
What if the INSERT had been issued by another session ? Would
the DROP TABLE go through
Hemant,
My guess is that Oracle, at some point in time long ago, decided that DDL's
and DML's should not be mixed together. Because they could not (or did not
want to) deal with the issue, they decided to perform an implicit commit
before any DDL statement was issued. Case closed. This is the
Tom - I think you've nailed it. Think of the design decisions that some of
Oracle's competitors made in the early days and how silly they seem in
retrospect. Anyone remember the row-locking vs. block-locking wars?
The other aspect that many people don't think of if they have never
worked in
are at a safe commit point.
jared
Mercadante, Thomas F [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/24/2003 09:34 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: DDLs -- was Re: simple question
Agreed. There's a lot of code out there that was developed considering the
way Oracle handled DDL and DML specifically.
Although I would have liked Oracle to handle DDL as an Autonomous Transaction
and provide an error message for the scenario in the first example, I guess
it might be too late
SORRY - this was the wrong list. the other pertinent list is one about controlling
machines via a DDL protocol and a demon called the alike.
kr mr
[EMAIL PROTECTED] 01/22/03 16:49 PM
hi
what's your exact question?
you mean autocommit like in database applications?
what tool are you using to
-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: simple question on DDL
Date: Wed, 22 Jan 2003 14:18:57 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by
mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed
recipients of
list
ORACLE-L [EMAIL PROTECTED]
.com cc:
Sent by: Subject: RE: simple
question
on
DDL
root@fatcity.
com
]
Subject: RE: simple question on DDL
Date: Wed, 22 Jan 2003 14:18:57 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by
mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed,
22 Jan
2003 15:13:04 -0800
Received: from fatcity.UUCP (uucp
thoughts on that?
Arup
From: Fink, Dan [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: simple question on DDL
Date: Wed, 22 Jan 2003 14:18:57 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by
mc1-f3
:
Sent by: Subject: RE: simple question on DDL
[EMAIL PROTECTED
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
Title: RE: simple question on DDL
From: Gogala, Mladen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 23, 2003 8:45 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: simple question on DDL
Actually, DB2 (pardon my French) doesn't issue a commit
after a DDL. I'm
]]
Sent: Thursday, January 23, 2003 10:05 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: simple question on DDL
Arup,
I see your point and agree that the DDL should be an
autonomous tx.
Perhaps an enhancement request is in order? Since Oracle has
the autonomous
tx code
Title: RE: simple question on DDL
Set autotrace on ... and then do explain plan for
You'll see.
Raj
__
Rajendra
Jamadagni
MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot
com
Any opinion expressed here is
personal and doesn't
One question to ask is whether whether all DDL
use the same strategy. Similarly, if you have
multiple code paths for do a ddl call how much
more risk of error do you introduce to the kernel.
Finally how do you get a consistent error response
to the end user if the error condition of apparently
Hi friends
Why DDL statements performs auto commit ? What is the exact reason behind
that one?
Anyone can share his/her opinions!!
Thanks regards
BanarasiBabu
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: BanarasiBabu Tippa
INET: [EMAIL PROTECTED]
Fat City
hi
what's your exact question?
you mean autocommit like in database applications?
what tool are you using to observe or redard to this phenomenon?
the erd-demon has to send some info via rs232 to make the amplifier -called booster -
work. no info implies no current on the tracks. kind of answwer
My guess will be to preserve the changes to the data dictionary, which are
just Oracle tables anyway. When you create a table, a record goes to TAB$,
SEG$ and so on and so forth. Unless there is a commit these information is
not visible.
But now that you asked, I wnder why the same objective
]
tmail.com cc:
Sent by: Subject: Re: simple question on DDL
root@fatcity
Consistency is the key too imagine what would happen if I dropped a
column or changed it's definition, while a SQL statement or PL/SQL package
was executing. The data that was updated before the change may well be very
different in nature than the data after the change
Borrowing from
arupnanda@hoTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
tmail.com cc:
Sent by: Subject: Re: simple question
on DDL
root@fatcity.
com
January 22
Take the case of an insert (we'll call tx1), where space allocation is
required. As you insert records, the table allocated additional extents
(updating fet$ (free extent table) and uet$ (used extent table) in the data
dictionary). These updates to the data dictionary are implicitly committed,
Very good case described.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, January 22, 2003 2:16 PM
Take the case of an insert (we'll call tx1), where space allocation is
required. As you insert
: RE: simple question on DDL
root@fatcity.
com
]
.comcc:
Sent by: Subject: RE: simple question on
DDL
root@fatcity.
com
January 22,
2003 02:16 PM
Please
: simple question on DDL
Date: Wed, 22 Jan 2003 14:18:57 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by
mc1-f3.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Wed, 22 Jan
2003 15:13:04 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com
(8.9.3
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
29 matches
Mail list logo