ORA-00054: resource busy and acquire with NOWAIT specified

1

案例：

客户想通过alter table def.d read only;的方式停表，然后expdp迁移走部分表。

语句找到sid/serial#

SELECT s.sid, s.serial#,s.username

FROM v$transaction t, v$session s,v$lock l

WHERE t.status = 'ACTIVE' AND t.addr=s.TADDR and l.sid=s.sid and l.id1 IN (SELECT OBJECT_ID FROM dba_objects WHERE object_name = 'D' and OWNER='DEF');

更简单（不用v$transaction）：

SELECT s.sid, s.serial#,s.username

FROM v$session s,v$lock l

WHERE l.sid=s.sid and l.id1 IN (SELECT OBJECT_ID FROM dba_objects WHERE object_name = 'D' and OWNER='DEF');

杀光即可。

alter system kill session '1147,25' immediate;

拼接语句

普通版

set linesize 300

col INST_ID for 99999

col OBJECT_NAME for a25

col kill for a60

select l.INST_ID,

o.OBJECT_NAME,

gs.SID,

gs.SERIAL#,

'alter system kill session ''' || gs.SID || ',' || gs.SERIAL# || ',@' ||

gs.INST_ID || ''' immediate;' kill

from gv$LOCKED_OBJECT l, dba_objects o, gv$session gs

where l.OBJECT_ID = o.OBJECT_ID

and o.OBJECT_NAME = 'D' and o.OWNER='DEF'

and l.SESSION_ID = gs.SID and l.INST_ID=gs.INST_ID;

互动输入版本

set linesize 300

col INST_ID for 99999

col OBJECT_NAME for a25

col kill for a60

select l.INST_ID,

o.OBJECT_NAME,

gs.SID,

gs.SERIAL#,

'alter system kill session ''' || gs.SID || ',' || gs.SERIAL# || ',@' ||

gs.INST_ID || ''' immediate;' kill

from gv$LOCKED_OBJECT l, dba_objects o, gv$session gs

where l.OBJECT_ID = o.OBJECT_ID

and o.OBJECT_NAME = upper('&table') and o.OWNER=upper('&owner')

and l.SESSION_ID = gs.SID and l.INST_ID=gs.INST_ID;