ORA-00054:资源正忙,要求指定NOWAIT
问题回顾：
创建索引的时候报错ORA-00054: resource busy and acquire with NOWAIT specified
解决步骤：
1：等待其他会话释放资源
2：找出占用资源的会话，并删除
3：重启数据库
原理分析：
1：创建索引时会产生的锁
2：dml 语句会产生的锁
3：索引创建时加上关键字 online时产生的锁
问题回顾

  1. 1
    创建索引时失败报错
    create index sa.idx_test_1_id on sa.test_1 (id);
    NOWAIT ：关键字表示sql语句采用非阻塞的方式,如果发现涉及到的数据被占有(被锁),则立即通知Oracle该资源被占用,返回错误信息

    方法/步骤2
  2. 1 等待其他会话释放资源 在创建语句中添加online，会话释放资源之后，该语句会自动执行。 create index sa.idx_test_1_id on sa.test_1 (id) online; 这种方式是采用阻塞方式，不报错 找出占用资源的会话，并删除 1：找出所有被锁的对象，定位出哪个回话占用
select l.session_id,o.owner,o.object_name
 from vKaTeX parse error: Expected 'EOF', got '#' at position 220: …2.sid,t2.serial#̲,t2.logon_time …locked_object t1,v$session t2
 where t1.session_id=t2.sid order by t2.logon_time;
 结果：
 username sid serial# logon_time
 SA 158 15184 2014/12/4 14:55:59
 SA 146 8229 2014/12/4 15:23:22
 SA 136 14314 2014/12/4 16:09:59
 SA 131 54 2014/12/4 16:10:06
 3：kill 所有占用资源的会话
 命令形式：alter system kill session ‘sid,serial#’;
 占用test_1的资源的会话：
 alter system kill session ‘158,15184’;
 alter system kill session ‘146 ,8229’;


  1. 重启数据库
    如果数据不重要的话，可以重启数据库回滚所有未提交事务，将资源释放出来
    END
    原理分析
  2. 创建索引时会产生的锁 1：查看当前回话号 SQL> select sid from v         mystat where rownum<2; 2：创建索引 SQL> create index sa.idx_clxsgj_HPHM_jgsj on sa.clxsgj(HPHM,jgsj); 3：查询当前会话号产生的锁 select rpad(oracle_username, 10) o_name, session_id sid, decode(locked_mode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_type, object_name, xidusn, xidslot, xidsqn from v 
mystat where rownum<2; 2：创建索引 SQL> create index sa.idx_clxsgj_HPHM_jgsj on sa.clxsgj(HPHM,jgsj); 3：查询当前会话号产生的锁 select rpad(oracle_username, 10) o_name, session_id sid, decode(locked_mode, 0, 'None', 1, 'Null', 2, 'Row share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive') lock_type, object_name, xidusn, xidslot, xidsqn from v
  1. locked_object, all_objects where v创建索引 会影响临时空间吗 创建索引时间过长_数据库表上产生 行级排他锁（Row Exclusive） 注释： 排他锁（row exclusive）：行级别，释放前，其他事物不能修改被锁的资源 共享锁（share）：段级别，释放之前，对象上可以继续加其他类型的锁 共享锁（row share）：行级别 同一个段级不能同时存在share和row exclusive， 同一个段级row share 和row exclusive 可以同时存在 段级：一个普通表、分区表的每个分区、普通索引、索引的每个分区 行级：一行数据 锁范围大小： EXCLUSIVE > ROW SHARE EXCLUSIVE > SHARE > ROW EXCLUSIVE > ROW SHARE
  2. dml 语句会产生的锁
    1：连接用
    SQL> conn sa/ednns
    2：执行DML语句，但不提交
    SQL> update CLXSGJ set cdfx=2 where clgjid=300000040044785;
    3：户查看当前回话号
    SQL> select sid from v$mystat where rownum<2;
    SID
140

4：查看DML语句产生的锁

select rpad(oracle_username, 10) o_name,
 session_id sid,
 decode(locked_mode,
 0,
 ‘None’,
 1,
 ‘Null’,
 2,
 ‘Row share’,
 3,
 ‘Row Exclusive’,
 4,
 ‘Share’,
 5,
 ‘Share Row Exclusive’,
 6,
 ‘Exclusive’) lock_type,
 object_name,
 xidusn,
 xidslot,
 xidsqn
 from vlocked_object.object_id = all_objects.object_id and session_id=140

从中可以看出

DML语句会在更新数据所在的分区上产生行级排他锁。

之前已经

由于DML语句在CLXSGJ某些分区已经行级排他锁，而create index会对所有分区产生段级共享锁，对象上已经存在的锁不允许比他大一级的锁产生。

创建索引 会影响临时空间吗 创建索引时间过长_database_02


3. 索引创建时加上关键字 online时产生的锁

加上online 对每个分区仅仅产生ROW SHARE锁，且不会对OBJ$表产生

ROW EXCLUSIVE，而是产生一个临时表，并在临时表中产生SHARE 锁

注释：

ROW EXCLUSIVE 可以允许比他小一级的ROW SHARE 锁产生，所以不会报错

创建索引 会影响临时空间吗 创建索引时间过长_创建索引 会影响临时空间吗_03