创建索引-资源正忙的解决方案及原理

GPS平台、网站建设、软件开发、系统运维,找森大网络科技! ​​https://cnsendnet.taobao.com​​ 来自森大科技官方博客 ​​http://www.cnsendblog.com/index.php/?p=2128​​ ​​分步阅读​​     ORA-00054:资源正忙,要求指定NOWAIT 问题回顾: 创建索引的时候报错ORA-00054:resource busy and acquire with NOWAIT specified 解决步骤: 1:等待其他会话释放资源 2:找出占用资源的会话,并删除  3:重启数据库 原理分析:     1:创建索引时会产生的锁 2:dml 语句会产生的锁 3:索引创建时加上关键字 online时产生的锁 问题回顾 1.      1 创建索引时失败报错 create indexsa.idx_test_1_id on sa.test_1 (id); NOWAIT :关键字表示sql语句采用非阻塞的方式,如果发现涉及到的数据被占有(被锁),则立即通知Oracle该资源被占用,返回错误信息 方法/步骤2 1.      1 等待其他会话释放资源 在创建语句中添加online,会话释放资源之后,该语句会自动执行。 create indexsa.idx_test_1_id on sa.test_1 (id) online; 这种方式是采用阻塞方式,不报错 找出占用资源的会话,并删除  1:找出所有被锁的对象,定位出哪个回话占用 select l.session_id,o.owner,o.object_name fromv$locked_object l,dba_objects o wherel.object_id=o.object_id 结果: session_id owner object_name 158 SA TEST_1 146 SA TEST_1 131 SA TEST_3 136 SA TEST_2 对比想要创建的索引,定位哪些会话需要被删除 2:找出所有照成锁的会话 selectt2.username,t2.sid,t2.serial#,t2.logon_time fromv$locked_object t1,v$session t2 wheret1.session_id=t2.sid order by t2.logon_time; 结果: username sid  serial# logon_time SA 158 151842014/12/4 14:55:59 SA 146 82292014/12/4 15:23:22 SA 136 143142014/12/4 16:09:59 SA 131 542014/12/4 16:10:06 3:kill 所有占用资源的会话 命令形式:alter system kill session 'sid,serial#'; 占用test_1的资源的会话: alter systemkill session '158,15184'; alter systemkill session '146 ,8229'; 重启数据库 如果数据不重要的话,可以重启数据库回滚所有未提交事务,将资源释放出来 END 原理分析 1.      创建索引时会产生的锁 1:查看当前回话号 SQL> selectsid from v$mystat where rownum createindex sa.idx_clxsgj_HPHM_jgsj on sa.clxsgj(HPHM,jgsj); 3:查询当前会话号产生的锁 selectrpad(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 RowExclusive', 6, 'Exclusive')lock_type, object_name, xidusn, xidslot, xidsqn fromv$locked_object, all_objects wherev$locked_object.object_id = all_objects.object_id  andsession_id=140 从图片中可以看出 在创建索引的时候,会在每个分区产生共享锁(share),并在OBJ$表上产生 行级排他锁(Row Exclusive) 注释: 排他锁(row exclusive):行级别,释放前,其他事物不能修改被锁的资源 共享锁(share):段级别,释放之前,对象上可以继续加其他类型的锁 共享锁(row share):行级别 同一个段级不能同时存在share和row exclusive, 同一个段级row share 和row exclusive 可以同时存在 段级:一个普通表、分区表的每个分区、普通索引、索引的每个分区 行级:一行数据 锁范围大小: EXCLUSIVE> ROW SHARE EXCLUSIVE > SHARE > ROW EXCLUSIVE > ROW SHARE  ​ 1.      dml 语句会产生的锁 1:连接用 SQL> connsa/ednns 2:执行DML语句,但不提交 SQL> updateCLXSGJ set cdfx=2 where clgjid=300000040044785; 3:户查看当前回话号 SQL> selectsid from v$mystat where rownum<2;       SID ----------       140 4:查看DML语句产生的锁 selectrpad(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 RowExclusive', 6, 'Exclusive')lock_type, object_name, xidusn, xidslot, xidsqn fromv$locked_object, all_objects wherev$locked_object.object_id = all_objects.object_id  andsession_id=140 从中可以看出 DML语句会在更新数据所在的分区上产生行级排他锁。 之前已经 由于DML语句在CLXSGJ某些分区已经行级排他锁,而create index会对所有分区产生段级共享锁,对象上已经存在的锁不允许比他大一级的锁产生。 ​ ​ 1.      索引创建时加上关键字 online时产生的锁 加上online 对每个分区仅仅产生ROW SHARE锁,且不会对OBJ$表产生 ROW EXCLUSIVE,而是产生一个临时表,并在临时表中产生SHARE 锁 注释: ROW EXCLUSIVE 可以允许比他小一级的ROW SHARE 锁产生,所以不会报错 ​ GPS平台、网站建设、软件开发、系统运维,找森大网络科技! ​​https://cnsendnet.taobao.com​​ 来自森大科技官方博客 ​​http://www.cnsendblog.com/index.php/?p=2128​​

锦绣源码库是一家优秀的网站源码学习交流平台,为广大源码爱好者提供优质的小程序源码、APP源码、H5源码、商城源码教程以及公众号模块教程,大部分是会员免费,网站长期受到各站长的收藏及浏览。
用户必须遵守《计算机软件保护条例(2013修订)》第十七条:为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬。鉴于此条例,用户从本平台下载的全部源码(软件)教程仅限学习研究,未经版权归属者授权不得商用,若因商用引起的版权纠纷,一切责任均由使用者自行承担,本平台所属公司及其雇员不承担任何法律责任。
锦绣源码库 » 创建索引-资源正忙的解决方案及原理
赞助VIP 享更多特权,立即登录下载海量资源
喜欢我嘛?喜欢就按“ctrl+D”收藏我吧!♡