插入数据的过程，drop index关系报资源忙

插入数据的过程，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;

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> 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博客 ” ，链接：http://blog.itpub.net/27573546/viewspace-761548/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27573546/viewspace-761548/