各位大侠,如标题所示,我在对数据库中的表A进行表结果修改时,提示“ora-00054:resource busy and acquire with NOWAIT specified”,看提示语似乎是表被锁了,但是我可以使用for update对表中的数据进行修改,就是对表结构无从修改,还是大侠们帮忙解决!小弟谢谢了!
表锁了,得先解锁啊。看看哪个session给表锁了: 执行下面语句查查: SELECT A.OWNER, A.OBJECT_NAME, B.XIDUSN, B.XIDSLOT, B.XIDSQN, B.SESSION_ID, B.ORACLE_USERNAME, B.OS_USER_NAME, B.PROCESS, B.LOCKED_MODE, C.MACHINE, C.STATUS, C.SERVER, C.SID, C.SERIAL#, C.PROGRAM FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C WHERE (A.OBJECT_ID = B.OBJECT_ID) AND (B.PROCESS = C.PROCESS) ORDER BY 1, 2
用上面的语句查出来的sid和serial来释放当前session的锁: alter system kill session 'sid, serial#';
修改表结构时,在表上不能有其它锁,而修改数据只要是修改的数据行不被锁就可以。 你先查询在该表上没有锁,再执行表修改的SQL: select * from v$locked_object where object_id in(select object_id from dba_objects where object_name='<tableName>'); 如果你的应用不间断的对该表做事务处理,那只有把应用停止才能修改表结构。
执行下面语句查查:
SELECT A.OWNER,
A.OBJECT_NAME,
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID,
B.ORACLE_USERNAME,
B.OS_USER_NAME,
B.PROCESS,
B.LOCKED_MODE,
C.MACHINE,
C.STATUS,
C.SERVER,
C.SID,
C.SERIAL#,
C.PROGRAM
FROM ALL_OBJECTS A, V$LOCKED_OBJECT B, SYS.GV_$SESSION C
WHERE (A.OBJECT_ID = B.OBJECT_ID)
AND (B.PROCESS = C.PROCESS)
ORDER BY 1, 2
alter system kill session 'sid, serial#';
你先查询在该表上没有锁,再执行表修改的SQL:
select * from v$locked_object where object_id in(select object_id from dba_objects where object_name='<tableName>');
如果你的应用不间断的对该表做事务处理,那只有把应用停止才能修改表结构。