Help - Search - Members - Calendar
Full Version: DDL statements fail or not when DML active?
Oracle DBA Forums > Oracle > Oracle Forum
kyriakos petrakos
Hello to everyone!

As far as I know, DDL statements automatically end with COMMIT the user transactions in which they appear. Foe example:

-------------------------------------------
create table mytable01 (i integer);

insert into mytable01
select 1 from dual;

create table mytable02 (i integer);
-------------------------------------------

After all three statements are executed, data are committed in mytable01.

In the Oracle DB server SQL guide we read:

"DDL commands, such as TRUNCATE, will fail if there is any DML command active on the table. A transaction will block the DDL command until the DML command is terminated with a COMMIT or a ROLLBACK."

But I executed the following without any problem:

-------------------------------------------
create table mytable (i integer);

insert into mytable
select 1 from dual;

commit;

update mytable
set i = 2;

alter table mytable add (j integer);
-------------------------------------------

So where's the truth? Are DDL statements blocked when they refer to an active object accessed from a DML or not?

Thanks!






SteveC
Create users x and y. In x, create a table and insert into it, do not commit;
In a session for y, try and do DDL against x's table. You will get resource busy error (x and y both granted dba role to make the setup easier/quicker to see).
kyriakos petrakos
QUOTE (SteveC @ Dec 9 2011, 05:58 PM) *
Create users x and y. In x, create a table and insert into it, do not commit;
In a session for y, try and do DDL against x's table. You will get resource busy error (x and y both granted dba role to make the setup easier/quicker to see).


So this means that blocking of DDL on the object on which a DML is active occurs across different sessions (when the DML and the DDL belong to different sessions) and not within the same one.

Thanks Steve!
burleson
Hi Kyriakos,

Your name is quite unique! Is Kyriakos an Easkimo name?

>> DDL statements automatically end with COMMIT the user transactions

It's not about a COMMIT, it about recoverability.

As many ex-DBA's know, you cannot ROLLBACK a "drop table"!


**************************************************
>> Are DDL statements blocked when they refer to an active object accessed from a DML or not?

Great question!

The DDL is not blocked, per se, but there must be no locks on the table at DDL time . . .

Let's assume that a task in an a loop, deleting rows from a table:

CODE
for i = 1 to EOF loop
   delete from mytab where var=:myvar(i)
end loop;


Until this task ends (or COMMIT's) a DDL to drop the table would have to wait.

However, I wonder if a DDL to dynamically add a column would have to wait for the DML to complete?

I once did an "alter table DDL" (I added a freelist) to a table while it was accepting DML. It worked at the time, but the table became corrupted a few minutes later!

The point is, just because you CAN do DDL during DML, does not mean that you should!

Of course, in the real-world this is a moot question, since dbms_redefinition would be used to make DDL changes when DML is running:

Please read:

http://www.dba-oracle.com/t_dbms_redefinition.htm

As you see, with dbms_redefinition the DML in enqueued until the DDL has completed . . .

http://www.dba-oracle.com/t_concurrent_dml_ddl.htm
kyriakos petrakos
Hello Donald and thank you for the reply,

QUOTE
Your name is quite unique! Is Kyriakos an Easkimo name?

Haha, in fact my name would sound equally exotic in Alaska as it sounds to you, it’s a Greek name meaning “Mr. Sunday”!

QUOTE
As many ex-DBA's know, you cannot ROLLBACK a "drop table"!

Rollbacking a dropped table makes no sense but the fact that you cannot rollback a DML statement proceeding a “drop table” when this DML statement does not refer to the dropped table is not intuitively obvious, this is more of an Oracle architecture thing (quoting from the SQL reference of the Oracle 11g documentation):

If a user issues a DDL or DCL, the transaction he has in progress (if any) will be committed. This is because the DDL and DCL commands are themselves transactions. They adjust the data structures by performing DML commands against the tables of the data dictionary, and these commands are terminated with a COMMIT.

Quoting from the same source:

A DROP is therefore absolutely nonreversible. But there are some restrictions: if any session (even your own) has a transaction in progress that includes a row in the table, then the DROP will fail.

I did not manage to force the DROP to fail by the same session while a DML (even a SELECT FOR UPDATE!) was active on the table, the only case it failed was if the DROP was issued by another session (with ORA-00054).

Thanks for pointing out the redefinition package, playing a bit with it I saw that many “interim” DDLs can be applied to the EMPLOYEES2 table (apart of course from a DROP!)

Regards, Kyriakos
burleson
Hi Kyriakos,

Mr. Sunday, Ha! I love that name!

************************************************
>> the fact that you cannot rollback a DML statement proceeding a “drop table” when this DML statement does not refer to the dropped table is not intuitively obvious,

Yeah, I agree.

Oracle is not a science, it'w written by people . . . Here is where Oracle hires their software developers:

http://www.dba-oracle.com/oracle_news/2006...niversities.htm

QUOTE
According to the e-mail, Oracle recruits "top candidates" for product development from MIT, Stanford, CMU (likely Carnegie Mellon University), Princeton, Wisconsin, Yale, Dartmouth, Brown, Caltech, Berkeley, Harvard and Cornell.


Some developer are "anal" about relational rules, others are not!

Years ago, you were not allowed to copy a table while resequencing the row order! (CTAS with ORDER BY) Oracle threw a silly message about how the physical placement of the rows on disk did not matter!

Remember, all relational vendors (Oracle, DB2, MS-SQL, Postgres) want to the "standarrd", but art the same time they want thrie product to "stand out", and they do it with non-relatioonal features.

When Dr. Peter Chen introduced 0NF, which later became VARRAY tables in Oracle 8 (non-first-normal-form) Chris Date threw a fit because it did not fit nicely into is "relational model"!

Read this, quite funny!

http://www.remote-dba.net/t_op_sql_relational_rules.htm


**********************************************
QUOTE
If a user issues a DDL or DCL, the transaction he has in progress (if any) will be committed. This is because the DDL and DCL commands are themselves transactions. They adjust the data structures by performing DML commands against the tables of the data dictionary, and these commands are terminated with a COMMIT.


This is true of most any statement, unless they allow a ROLBACK, which is often impossible when you adjust data structures in hundreds of data blocks!

[quote]
But there are some restrictions: if any session (even your own) has a transaction in progress that includes a row in the table, then the DROP will fail.
[quote]

Interesting, but it makes sense . . .

I've been a DBA since 1983, and trust me things change with almost every release!

Tell me, Kyriakos, what's it like being a DBA in Greece?

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.