Question: I
need to know if I can do table DDL (alter table statements) while
DML (inserts, updates deletes) are running? In some cases I
believe that the DML must commit before the DDL happens, but in
other cases, DML can be in progress when the DML occurs.
Are
DDL statements blocked when they refer to an active object accessed
from a DML or not?
Answer: Great
question! It depends on the type of DDL. All drop
table/index DDL operations must wait for DML to complete
Delete DDL is not blocked, per se, but there must be no locks on the
table at DDL drop table or drop user time.
Let's assume that a task in an a loop, deleting rows from a
table:
for i = 1 to EOF loop
delete from mytab where var=:myvar(i)
end loop;
Until this
delete DML task ends (or COMMIT's), a DDL drop table would
have to wait.
However, Would an alter table DDL to
dynamically add a column would have to wait for the DML to complete?
I once did an "alter table" DDL (I add a freelist) to a table
while it was accepting DML. It worked at the time, but to the table
became corrupted a few minutes later!
The point is, just
because you CAN do DDL during DML, this 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: How to do concurrent DML/DDL
with
dbms_refdefinition.
As you
see, with dbms_redefinition the DML in enqueued in a snapshot object
until the DDL has completed.
Quoting
from the SQL reference of the Oracle 11g documentation, we see that
DDL and DCL are considered discrete transactions just like
DML, and they will COMMIT upon completion::
"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. The
[DDL] adjusts the data structures by performing DML commands against
the tables of the data dictionary, and these DML commands must be
terminated with a COMMIT.
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."
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
