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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1822558/

总结

以上是生活随笔为你收集整理的oracle11g基于bootstrap$中的ind$表损坏系列五的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。