本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger 的Oracle技术博客 本文链接地址: How to drop a Index with bbed? 这是oracle恢复课程的一点内容，有朋友在问，所以就贴出来，其实没有什么，很简单，供大家参考！ 在某些情况下，o
本站文章除注明转载外，均为本站原创： 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: How to drop a Index with bbed?
这是oracle恢复课程的一点内容，有朋友在问，所以就贴出来，其实没有什么，很简单，供大家参考！
在某些情况下，oracle的bootstrap$的一些对象出现异常之后，比如Index。我们无法进行rebuild，或许只能用一些极端的手段去将Index drop然后重建（当然还有其他的方法）。这里是利用bbed来drop Index的例子！
++++ 创建测试表SQL> conn roger/rogerConnected.SQL> create table test_0824 as select object_id,object_name from dba_objects where rownum create index idx_test_0824 on test_0824(object_id);Index created.SQL>SQL> set autot traceonly expSQL> set lines 200SQL> select count(object_id) from test_0824;Execution Plan----------------------------------------------------------Plan hash value: 3721076966---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 || 1 | SORT AGGREGATE | | 1 | 13 | | || 2 | INDEX FAST FULL SCAN| IDX_TEST_0824 | 499 | 6487 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------------------Note----- - dynamic sampling used for this statementSQL> SQL> select object_id from dba_objects where object_name='IDX_TEST_0824'; OBJECT_ID---------- 52969SQL> select dbms_rowid.rowid_object(rowid) obj#, 2 dbms_rowid.rowid_relative_fno(rowid) rfile#, 3 dbms_rowid.rowid_block_number(rowid) block#, 4 dbms_rowid.rowid_row_number(rowid) row# 5 from sys.ind$ where obj#=52969; OBJ# RFILE# BLOCK# ROW#---------- ---------- ---------- ---------- 2 1 21519 0SQL> conn /as sysdbaConnected.SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL>
可以看到，上面的SQL走了Index fast full scan，现在我们的目的就是把这个Index drop掉，让其走full table scan。
下面我们开始利用BBED来写进行修改，如下步骤：
+++++Session 2BBED> set file 1 block 21519 FILE# 1 BLOCK# 21519BBED> map File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Dba:0x0040540f------------------------------------------------------------ KTB Data Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 72 bytes @20 struct kdbh, 14 bytes @92 struct kdbt[6], 24 bytes @106 sb2 kdbr[6] @130 ub1 freespace[7236] @142 ub1 rowdata[810] @7378 ub4 tailchk @8188 BBED> p *kdbr[0]rowdata[384]------------ub1 rowdata[384] @7762 0xacBBED> x /rnrowdata[384] @7762------------flag@7762: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)lock@7763: 0x00cols@7764: 1kref@7765: 5mref@7767: 5hrid@7769:0x0040540f.0nrid@7775:0x0040540f.0col 0[4] @7781: 52968 BBED> p *kdbr[1]rowdata[256]------------ub1 rowdata[256] @7634 0x6cBBED> x /rnrowdata[256] @7634------------flag@7634: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)lock@7635: 0x00cols@7636: 36col 0[4] @7638: 52968col 1[2] @7643: 6col 2[2] @7646: 5col 3[4] @7649: 12363col 4[0] @7654: *NULL*col 5[0] @7655: *NULL*col 6[2] @7656: 2col 7[0] @7659: *NULL*col 8[2] @7660: 10col 9[2] @7663: 40col 10[2] @7666: 1col 11[3] @7669: 255col 12[6] @7673: 1073741825col 13[38] @7680: 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2d 0x2dcol 14[0] @7719: *NULL*col 15[0] @7720: *NULL*col 16[0] @7721: *NULL*col 17[0] @7722: *NULL*col 18[0] @7723: *NULL*col 19[0] @7724: *NULL*col 20[0] @7725: *NULL*col 21[0] @7726: *NULL*col 22[0] @7727: *NULL*col 23[0] @7728: *NULL*col 24[0] @7729: *NULL*col 25[0] @7730: *NULL*col 26[2] @7731: 2col 27[2] @7734: 2col 28[6] @7737: 536870912col 29[1] @7744: 0col 30[3] @7746: 736col 31[0] @7750: *NULL*col 32[0] @7751: *NULL*col 33[0] @7752: *NULL*col 34[0] @7753: *NULL*col 35[7] @7754: ######################################### BBED> p *kdbr[2]rowdata[0]----------ub1 rowdata[0] @7378 0x6cBB<a style="color:transparent">本文来源gao($daima.com搞@代@#码$网</a>ED> x /rnrowdata[0] @7378----------flag@7378: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)lock@7379: 0x02cols@7380: 33col 0[4] @7382: 52969col 1[4] @7387: 52969col 2[2] @7392: 6col 3[2] @7395: 5col 4[4] @7398: 12371col 5[1] @7403: 0col 6[2] @7405: 1col 7[2] @7408: 10col 8[2] @7411: 2col 9[3] @7414: 255col 10[0] @7418: *NULL*col 11[2] @7419: 1col 12[2] @7422: 2col 13[1] @7425: 0col 14[2] @7427: 1col 15[2] @7430: 2col 16[3] @7433: 499col 17[2] @7437: 1col 18[2] @7440: 1col 19[2] @7443: 2col 20[7] @7446: #########################################col 21[3] @7454: 499col 22[3] @7458: 499col 23[2] @7462: 1col 24[0] @7465: *NULL*col 25[0] @7466: *NULL*col 26[0] @7467: *NULL*col 27[2] @7468: 1col 28[0] @7471: *NULL*col 29[0] @7472: *NULL*col 30[0] @7473: *NULL*col 31[0] @7474: *NULL*col 32[7] @7475: ######################################### BBED> modify /x 04 offset 7767Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 7767 to 7770 Dba:0x0040540f------------------------------------------------------------------------ 04000040 BBED> p *kdbr[2]rowdata[0]----------ub1 rowdata[0] @7378 0x6cBBED> d /v offset 7378 count 4 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 7378 to 7381 Dba:0x0040540f------------------------------------------------------- 6c022100 l l.!. BBED> modify /x 7c offset 7378 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 7378 to 7381 Dba:0x0040540f------------------------------------------------------------------------ 7c022100 BBED> sum applyCheck value for File 1, Block 21519:current = 0x6944, required = 0x6944BBED> verifyDBVERIFY - Verification startingFILE = /home/ora10g/oradata/aux/system01.dbfBLOCK = 21519Block Checking: DBA = 4215823, Block Type = KTB-managed data blockdata header at 0xb7e9125ckdbchk: the amount of space used is not equal to block size used=357 fsc=0 avsp=7638 dtl=8096Block 21519 failed with check code 6110DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0BBED>BBED> p kdbhstruct kdbh, 14 bytes @92 ub1 kdbhflag @92 0x00 (NONE) b1 kdbhntab @93 6 b2 kdbhnrow @94 6 sb2 kdbhfrre @96 -1 sb2 kdbhfsbo @98 50 sb2 kdbhfseo @100 7286 b2 kdbhavsp @102 7638 b2 kdbhtosp @104 7638 ++++8096-357=7739（转为16进制为1E3B）BBED> d /v offset 102 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 102 to 105 Dba:0x0040540f------------------------------------------------------- d61dd61d l BBED> modify /x 3b1e offset 102 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 102 to 105 Dba:0x0040540f------------------------------------------------------------------------ 3b1ed61d BBED> modify /x 3b1e offset 104 File: /home/ora10g/oradata/aux/system01.dbf (1) Block: 21519 Offsets: 104 to 107 Dba:0x0040540f------------------------------------------------------------------------ 3b1e0000 BBED> sum applyCheck value for File 1, Block 21519:current = 0x6944, required = 0x6944BBED> verifyDBVERIFY - Verification startingFILE = /home/ora10g/oradata/aux/system01.dbfBLOCK = 21519Block Checking: DBA = 4215823, Block Type = KTB-managed data blockdata header at 0xb7e9125ckdbchk: space available on commit is incorrect tosp=7739 fsc=0 stb=4 avsp=7739Block 21519 failed with check code 6111DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：How to drop a Index with bbed?
转载请注明原文链接：How to drop a Index with bbed?
