插入数据的过程，drop index关系报资源忙
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
-- Created on 2013/5/20 by VENCENT
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..10000000 loop
insert into test_a values(j,'mac'||j);
commit;
end loop;
end;
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
-- Created on 2013/5/20 by VENCENT
declare
-- Local variables here
i integer;
begin
-- Test statements here
for j in 1..10000000 loop
insert into test_a values(j,'mac'||j);
commit;
end loop;
end;
SQL> col owner for a12
SQL> col object_name for a16
SQL> select b.owner,b.object_name,l.session_id,l.locked_mode
2 from v$locked_object l, dba_objects b
3 where b.object_id=l.object_id
4 /
OWNER OBJECT_NAME SESSION_ID LOCKED_MODE
------------ ---------------- ---------- -----------
BTUPAYPROD TEST_A 21 3
SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time
2 from v$locked_object t1,v$session t2
3 where t1.session_id=t2.sid order by t2.logon_time
4 /
USERNAME SID SERIAL# LOGON_TIME
------------------------------ ---------- ---------- -----------
BTUPAYPROD 21 180 2013/5/20 1
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
5 8 1078 14094 21 BTUPAYPROD vencent 26044:28360 3
SQL>
SQL> col object_name for a16
SQL> select b.owner,b.object_name,l.session_id,l.locked_mode
2 from v$locked_object l, dba_objects b
3 where b.object_id=l.object_id
4 /
OWNER OBJECT_NAME SESSION_ID LOCKED_MODE
------------ ---------------- ---------- -----------
BTUPAYPROD TEST_A 21 3
SQL> select t2.username,t2.sid,t2.serial#,t2.logon_time
2 from v$locked_object t1,v$session t2
3 where t1.session_id=t2.sid order by t2.logon_time
4 /
USERNAME SID SERIAL# LOGON_TIME
------------------------------ ---------- ---------- -----------
BTUPAYPROD 21 180 2013/5/20 1
SQL> select * from v$locked_object;
XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS LOCKED_MODE
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------ -----------
5 8 1078 14094 21 BTUPAYPROD vencent 26044:28360 3
SQL>
SQL> drop index i_test_a_b;
drop index i_test_a_b
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
SQL> drop index i_test_a_a10;
drop index i_test_a_a10
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/27573546/viewspace-761548/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Oracle数据库规范:SQL开发规范
下一篇：
11g参数（含隐式）
请登录后发表评论
登录
全部评论
