alter table xx disable table lock问题

[超叔csdn]

为了防止误删除，我用如下语句加了锁：alter table xx disable table lock;

这个星期我想更新这个表，在解锁的时候提示我: resource busy and acquire with nowait specified

解锁用的这个：alter table xx enable table lock;

请问是哪里用错了吗，请高手解答!

[爱吃芒果的猴子]

主要是因为有事务正在执行（或者事务已经被锁），所以导致执行不成功。
用dba权限的用户查看数据库都有哪些锁
根据sid查看具体的sql语句，如果sql不重要，可以kill
kill该事务

[crazylaa]

因为有事务没有commit或rollback，所以资源正忙，导致alter取不到exclusive lock。
跟加锁解锁没关系。
参考：

解决ORA-00054: resource busy 的一种途径
2008-11-20 09:35
最近工作中遇到了一个小问题。在对table做ddl的时候遇到错误ORA-00054: resource busy and acquire with NOWAIT specified。这个错误很好理解也很好模拟出来。下面给出一种解决方法

首先我们来模拟一下现实的情况:

sesssion A :

SQL> create table tt1(x int);

Table created.


SQL> insert into tt1 values(1);

1 row created.

session B:

SQL> alter table tt1 modify (x default -1);
alter table tt1 modify (x default -1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

session B 想将x的default值改为-1时需要获得table的exclusive lock（其他的一些想获得table的exclusive lock的操作也一样），因为session A没有提交，所以session B 会失败。

在真实环境中我们无法控制其他的session何时提交，也不知道有哪些新的session会进来，所以只能想到通过写一段小程式来解决。

declare
default_v1 varchar2(20);
b_time varchar2(20);
e_time varchar2(20);
do_count number;
begin
dbms_output.enable(1000000);
do_count := 0;
select to_char(sysdate,'YYYYMMDD HH24MISS') into b_time from dual;
dbms_output.put_line('begin at: '||b_time);
loop
select data_default into default_v1 from user_tab_columns
where table_name='TT1' and column_name='X';

if default_v1 is null then
begin
do_count := do_count+1;
dbms_application_info.set_client_info('Try the ' || do_count||'th time');
execute immediate 'alter table TT1 modify (X default -1)';
exception
when others then
null;
end;
else
select to_char(sysdate,'YYYYMMDD HH24MISS') into e_time from dual;
dbms_output.put_line('finished at: '||e_time);
exit;
end if;
dbms_lock.sleep(1);
end loop;
end;
/

每隔一秒中尝试去获得exclusive lock。另外将尝试的次数通过dbms_application_info.set_client_info写入到client_info中。可以从v$session中client_info中读出来。

当然对于繁忙的系统，这样频繁的尝试也得不到exclusive lock，所以如果尝试一段时间后还不能获得的话，应该考虑放到下次系统停机的时间来执行。

[liusong_china]

应该是这个表被其他用户锁住了. 如select ... for update nowait

查看一下都有哪些锁，释放掉应该就可以了。

[超叔csdn]

不是啊，我在同一个session中执行这2个alter语句都会有问题。
貌似都做不出来，上面的信息是一个朋友向我求助，可惜我也没碰到他那种情况。

[crazylaa]

[Quote=引用 4 楼 sbaz 的回复:]
不是啊，我在同一个session中执行这2个alter语句都会有问题。
貌似都做不出来，上面的信息是一个朋友向我求助，可惜我也没碰到他那种情况。
[/Quote]
同一个session，测试了下，貌似没问题。

SQL> create table test(id number);

表已创建。

SQL> insert into test values(1);

已创建 1 行。

SQL> commit;

提交完成。

SQL> alter table test disable table lock;

表已更改。

SQL> alter table test enable table lock;

表已更改。

SQL> alter table test disable table lock;

表已更改。

SQL> drop table test;
drop table test
           *
ERROR 位于第 1 行:
ORA-00069: cannot acquire lock -- table locks disabled for TEST


SQL> insert into test values(2);

已创建 1 行。

SQL> alter table test enable table lock;

表已更改。

SQL> drop table test;

表已丢弃。

[wh62592855]

TABLE LOCK

Oracle Database permits DDL operations on a table only if the table can be locked during the operation. Such table locks are not required during DML operations.

Note:

Table locks are not acquired on temporary tables.

ENABLE TABLE LOCK
Specify ENABLE TABLE LOCK to enable table locks, thereby allowing DDL operations on the table. All currently executing transactions must commit or roll back before Oracle Database enables the table lock.
Caution:
Oracle Database waits until active DML transactions in the database have completed before locking the table. Sometimes the resulting delay is considerable.

DISABLE TABLE LOCK
Specify DISABLE TABLE LOCK to disable table locks, thereby preventing DDL operations on the table.

[wh62592855]

我刚也在同一个session里测试了一下好像没什么问题

[超叔csdn]

SQL> create table qc_pivot3 tablespace bidm_data as select * from qc_pivot;

Table created.

SQL> commit;

Commit complete.

SQL> alter table qc_pivot3 disable table lock;

Table altered.

SQL> alter table qc_pivot3 enable table lock;

这步就出不来了。

[超叔csdn]

怪哉。为何我在同一session下测试都有问题。
是不是与数据库配置有关？

不知道有没有哪位测试的时候遇到我同样的情况？

[wh62592855]

呵呵 要不你把你的测试代码贴上来看看