did you know you may disable table locks ?

January 8, 2013 — Leave a comment

just noticed that you can disable table locks and thus prevent ddl on a table in oracle.

create table t1 ( a number );
alter table t1 disable table lock;

according to the documentation this should prevent all ddls on this table. let’s see if it works:

alter table t1 modify ( a number(1,0 ));
alter table t1 modify ( a number(1,0 ))
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1

works if I try to modify a column definition. can you drop the table ?

drop table t1;
drop table t1
           *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1

ok, for dropping, too. truncating ?

truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1

what about adding a column ?

alter table t1 add ( b number );
Table altered.

hm…not sure if this is a bug, but think so ( this is 11.2.0.3 ). and an alter statement for sure is ddl.

on a 10.2.0.4 this does not work:

select version from v$instance;
VERSION
-----------------
10.2.0.4.0
alter table t1 add ( b number );
alter table t1 add ( b number )
*
ERROR at line 1:
ORA-00069: cannot acquire lock -- table locks disabled for T1
Advertisements

No Comments

Be the first to start the conversation!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s