oracle11g基於bootstrap$中的ind$表損壞系列五
結論
1,如果在ind$刪除對應測試表的索引條目，查詢應用表不全再使用索引2,ind$表的flags以及相關幾個列，控制索引是否可用
3,ind$的定義非常複雜，值得全面學習
4,ind$如下列控制從哪些表空間及資料檔案和資料塊開始讀取索引的真正資料
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */ --索引的頭塊
6,如果上述列的資料不一致，可能會報
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [4], [5], [999724], [], [], [], [], [], [], [], []
7,經過對比分析，可知第2個引數就是表空間，第3個引數是資料檔案，第4個引數是索引的塊頭
測試
1,資料庫版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
2，建立測試表，插入資料，構建索引
SQL> create table t_ind_err(a int,b int);
Table created.
SQL> insert into t_ind_err select level,level from dual connect by level<=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> create index idx_t_ind_err on t_ind_err(a);
Index created.
3，查詢測試表SQL
SQL> set autot traceonly
SQL> select * from t_ind_err where a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
4,刪除ind$的對應記錄
SQL> select obj#,name from obj$ where lower(name)='idx_t_ind_err';
OBJ# NAME
---------- ------------------------------------------------------------
75125 IDX_T_IND_ERR
ind$表的DDL定義
create table ind$ /* index table */
( obj# number not null, /* object number */
/* DO NOT CREATE INDEX ON DATAOBJ# AS IT WILL BE UPDATED IN A SPACE
* TRANSACTION DURING TRUNCATE */
dataobj# number, /* data layer object number */
ts# number not null, /* tablespace number */
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */ --索引的頭塊
bo# number not null, /* object number of base table */
indmethod# number not null, /* object # for cooperative index method */
cols number not null, /* number of columns */ --索引包括幾個列
pctfree$ number not null, /* minimum free space percentage in a block */
initrans number not null, /* initial number of transaction */
maxtrans number not null, /* maximum number of transaction */
pctthres$ number, /* iot overflow threshold, null if not iot */
type# number not null, /* what kind of index is this? */ --索引的型別，普通還是點陣圖
/* normal : 1 */
/* bitmap : 2 */
/* cluster : 3 */
/* iot - top : 4 */
/* iot - nested : 5 */
/* secondary : 6 */
/* ansi : 7 */
/* lob : 8 */
/* cooperative index method : 9 */
flags number not null, ---索引的是否分析，是否可用，是否重建，諸多資訊，豐富很多
/* mutable flags: anything permanent should go into property */
/* unusable (dls) : 0x01 */
/* analyzed : 0x02 */
/* no logging : 0x04 */
/* index is currently being built : 0x08 */
/* index creation was incomplete : 0x10 */
/* key compression enabled : 0x20 */
/* user-specified stats : 0x40 */
/* secondary index on IOT : 0x80 */
/* index is being online built : 0x100 */
/* index is being online rebuilt : 0x200 */
/* index is disabled : 0x400 */
/* global stats : 0x800 */
/* fake index(internal) : 0x1000 */
/* index on UROWID column(s) : 0x2000 */
/* index with large key : 0x4000 */
/* move partitioned rows in base table : 0x8000 */
/* index usage monitoring enabled : 0x10000 */
/* 4 bits reserved for bitmap index version : 0x1E0000 */
/* Delayed Segment Creation: 0x4000000 */
property number not null, /* immutable flags for life of the index */ --索引的型別
/* unique : 0x01 */
/* partitioned : 0x02 */
/* reverse : 0x04 */
/* compressed : 0x08 */
/* functional : 0x10 */
/* temporary table index: 0x20 */
/* session-specific temporary table index: 0x40 */
/* index on embedded adt: 0x80 */
/* user said to check max length at runtime: 0x0100 */
/* domain index on IOT: 0x0200 */
/* join index : 0x0400 */
/* system managed domain index : 0x0800 */
/* The index was created by a constraint : 0x1000 */
/* The index was created by create MV : 0x2000 */
/* composite domain index : 0x8000 */
/* The following columns are used for index statistics such
* as # btree levels, # btree leaf blocks, # distinct keys,
* # distinct values of first key column, average # leaf blocks per key,
* clustering info, and # blocks in index segment.
*/
blevel number, /* btree level */
leafcnt number, /* # of leaf blocks */
distkey number, /* # distinct keys */
lblkkey number, /* avg # of leaf blocks/key */
dblkkey number, /* avg # of data blocks/key */
clufac number, /* clustering factor */
analyzetime date, /* timestamp when last analyzed */
samplesize number, /* number of rows sampled by Analyze */
rowcnt number, /* number of rows in the index */
intcols number not null, /* number of internal columns */
/* The following two columns are only valid for partitioned indexes */
/*
* Legal values for degree, instances:
* NULL (used to represent 1 on disk/dictionary and implies noparallel), or
* 2 thru EB2MAXVAL-1 (user supplied values), or
* EB2MAXVAL (implies use default value)
*/
degree number, /* number of parallel query slaves per instance */ --索引的並行度
instances number, /* number of OPS instances for parallel query */
trunccnt number, /* re-used for iots 'inclcol' */
spare1 number, /* number of columns depended on, >= intcols */
spare2 number, /* number of key columns in compressed prefix */
spare3 number,
spare4 varchar2(1000), /* used for parameter str for domain idx */
spare5 varchar2(1000),
spare6 date /* flashback timestamp */ --閃回技術
)
cluster c_obj#(bo#)
SQL> select obj#,type#,flags from ind$ where obj#=75125;
OBJ# TYPE# FLAGS
---------- ---------- ----------
75125 1 2
SQL> create table ind$_bak as select * from ind$ where obj#=75125;
Table created.
SQL> delete from ind$ where obj#=75125;
1 row deleted.
SQL> commit;
Commit complete.
奕成了全表掃描
SQL> alter system flush shared_pool;
System altered.
SQL> set autot traceonly
SQL> select * from t_ind_err where a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3215397359
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_IND_ERR | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
289 recursive calls
0 db block gets
48 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
恢復ind$
SQL> insert into ind$ select * from ind$_bak;
1 row created.
SQL> commit;
Commit complete.
恢復後又使用了索引
SQL> select * from t_ind_err where a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
360 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
5,還下我們不刪除掉整條ind$記錄，我們更新其記錄對應列值，看會如何
SQL> select obj#,cols from ind$ where obj#=75125;
OBJ# COLS
---------- ----------
75125 1
SQL> update ind$ set cols=2 where obj#=75125;
1 row updated.
SQL> commit;
Commit complete.
雖說更新了ind$對應記錄的某列，仍可以使用索引
SQL> select * from t_ind_err where a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
360 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
說明ind$僅有些重要列比如flags，會決定應用的索引是否有效
SQL> update ind$ set cols=1,flags=1 where obj#=75125;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from t_ind_err where a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3215397359
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_IND_ERR | 1 | 26 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
356 recursive calls
0 db block gets
64 consistent gets
0 physical reads
0 redo size
585 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
6,還原ind$表資料
SQL> update ind$ set cols=1,flags=2 where obj#=75125;
1 row updated.
SQL> commit;
Commit complete.
7,繼續學習下ind$幾個重要的列含義
SQL> select obj#,ts#,file#,block# from ind$ where obj#=75125;
OBJ# TS# FILE# BLOCK#
---------- ---------- ---------- ----------
75125 4 4 100434
file# number not null, /* segment header file number */
block# number not null, /* segment header block number */ --索引的頭塊
SQL> update ind$ set block#=999724 where obj#=75125;
1 row updated.
SQL> commit;
Commit complete.
看到沒，查詢應用表報錯了
SQL> set autot traceonly
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [4], [4], [999724], [], [], [], [], [], [], [], []
由上可知，999724就是要訪問的索引的頭塊
2個4各是表空間和資料檔案
那麼哪個是表空間，哪個是資料檔案呢，我們對比下，即知
SQL> update ind$ set ts#=6,file#=3 where obj#=75125;
1 row updated.
SQL> commit;
Commit complete.
直接去找表空間6，即soe可知不存在
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00959: tablespace 'SOE' does not exist
SQL> update ind$ set ts#=4,file#=5 where obj#=75125;
1 row updated.
SQL> commit;
Commit complete.
可知第2個引數就是表空間，第3個引數是資料檔案，第4個引數是索引的塊頭
SQL> select * from t_ind_err where a=1;
select * from t_ind_err where a=1
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktsircinfo_num1], [4], [5], [999724], [], [], [], [], [], [], [], []
恢復ind$
SQL> update ind$ set ts#=4,file#=4,block#=100434 where obj#=75125;
1 row updated.
SQL> commit;
執行計劃恢復正常
SQL> select * from t_ind_err where a=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3275211715
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_IND_ERR | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_IND_ERR | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
360 recursive calls
0 db block gets
61 consistent gets
0 physical reads
0 redo size
589 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
Commit complete.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1822558/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 11g如果應用表依賴的bootstrap$某些某表如obj$_col$資料損壞系列四OraclebootOBJ
- Bootstrap系列 -- 11. 基礎表單boot
- oracle10g rac 表決盤損壞、ocr損壞處理Oracle
- UNDO表空間損壞的處理
- 基於 Bootstrap 的響應式後臺儀表板boot
- 電腦硬碟分割槽表損壞怎麼修復？電腦硬碟分割槽表損壞的修復方法硬碟
- undo表空間損壞的處理過程
- UNDO 表空間檔案損壞的恢復
- 基於Bootstrap的標籤頁元件bootstrap-tabboot元件
- Bootstrap系列 -- 12. 水平表單boot
- 資料檔案或者tablespace損壞基於rman恢復測試
- MySQL資料庫表損壞後的修復方法MySql資料庫
- sysaux表空間檔案損壞的處理(zt)UX
- PostgreSQL資料庫toast表損壞解決SQL資料庫AST
- Oracle資料庫塊的物理損壞與邏輯損壞Oracle資料庫
- 【恢復】基於使用者管理的熱備份對非系統表空間的損壞進行恢復
- 基於Bootstrap的後臺管理皮膚：Bootstrap Metro Dashboardboot
- 【中亦安圖】關於資料庫檔案損壞風險的提醒(3)資料庫
- SQL Server中的dbcc ind命令用法SQLServer
- Bootstrap系列 -- 13. 內聯表單boot
- Bootstrap系列 -- 21. 表單提示資訊boot
- ORACLE中修復資料塊損壞Oracle
- MYSQL資料表損壞的原因分析和修復方法MySql
- system表空間檔案損壞----完全恢復
- Oracle REDO損壞Oracle Redo
- Bootstrap系列 -- 41. 帶表單的導航條boot
- 臨時表空間資料檔案損壞的解決
- INDEX表空間檔案丟失或者損壞的恢復Index
- 基於 Bootstrap 的 UI 擴充套件：StyleBootstrapbootUI套件
- Win7系統ISP損壞怎麼辦？Win7系統中lsp損壞的修復方法Win7
- Win7系統LSP損壞怎麼辦？Win7系統中LSP損壞的修復方法Win7
- oracle資料庫損壞的恢復過程-基於IBM伺服器儲存Oracle資料庫IBM伺服器
- 當前日誌損壞的案例
- 段頭損壞的處理
- 資料塊損壞ORA-1578(發現損壞塊)
- redo日誌損壞
- index損壞恢復Index
- undo 檔案損壞