定时收集Oracle索引信息

需求

• 把Oracle各实例的索引信息，统一收集存储到一张表里

建表

在dboop库中建表

CREATE TABLE `info_indexs` (
  `dbid` int NOT NULL DEFAULT '0',
  `table_name` varchar(255) NOT NULL DEFAULT '',
  `index_name` varchar(255) NOT NULL DEFAULT '',
  `index_type` varchar(64) NOT NULL DEFAULT '',
  `uniq_type` varchar(64) NOT NULL DEFAULT '',
  `num_rows` int NOT NULL DEFAULT 0,
  `sample_size` int NOT NULL DEFAULT 0,
  `last_analyzed` datetime null,
  `column_name` varchar(2000) NOT NULL DEFAULT '',
  `cstatus` smallint NOT NULL DEFAULT '1',
  `dba_freshtime` datetime NOT NULL DEFAULT '1990-01-01 00:00:00',
  PRIMARY KEY  (`dbid`,`table_name`,`index_name`),
  KEY `idx_info_indexs_time` (`dba_freshtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

实现数据字典收集入库

建Oracle采集任务

 <!--抓索引信息 --> 
<action result="isok,okcount,errcount,errmess" type="sql_to_sql">
<from_server>linkdb:{dbid}</from_server>
  <isadmin>1</isadmin>
<to_server>link:dboop</to_server> 
  <from_formatstr>maxrow:9999999</from_formatstr>
<from_sqlstr>
select i.table_name,i.index_name,i.index_type,i.uniqueness,num_rows,SAMPLE_SIZE,last_analyzed, t.column_name
from dba_indexes i join (
select TABLE_NAME,index_name, LISTAGG(column_name, ',') WITHIN GROUP(ORDER BY column_name)  as column_name
from dba_ind_columns where Table_owner=:0
group by TABLE_NAME,index_name
) t 
on t.index_name = i.index_name  and i.TABLE_NAME=t.TABLE_NAME
where i.owner=:0
  </from_sqlstr>
  <from_sqlpara>{dbname}</from_sqlpara>
    <to_sqlstr>insert into info_indexs
    (dbid,table_name,index_name,index_type,uniq_type,num_rows,sample_size,last_analyzed,column_name,cstatus,dba_freshtime)
      values(%s,%s,%s,%s,%s,%s,%s,%s,%s,1,%s)
         on duplicate key update  
      index_type=%s,uniq_type=%s,num_rows=%s,sample_size=%s,last_analyzed=%s,column_name=%s,
      cstatus=2,dba_freshtime=%s       
    </to_sqlstr>
<to_sqlpara>{dbid}</to_sqlpara>
<to_sqlpara>{0}</to_sqlpara>
<to_sqlpara>{1}</to_sqlpara>
<to_sqlpara>{2}</to_sqlpara> <!--index_type-->
<to_sqlpara>{3}</to_sqlpara> <!--uniqueness-->
<to_sqlpara>{4}</to_sqlpara> <!--num_rows-->
<to_sqlpara>{5}</to_sqlpara> <!--SAMPLE_SIZE-->
<to_sqlpara>{6}</to_sqlpara> <!--last_analyzed-->
<to_sqlpara>{7}</to_sqlpara> <!--column_name-->
<to_sqlpara>{tasktime}</to_sqlpara>
<to_sqlpara>{2}</to_sqlpara> <!--index_type-->
<to_sqlpara>{3}</to_sqlpara> <!--uniqueness-->
<to_sqlpara>{4}</to_sqlpara> <!--num_rows-->
<to_sqlpara>{5}</to_sqlpara> <!--SAMPLE_SIZE-->
<to_sqlpara>{6}</to_sqlpara> <!--last_analyzed-->
<to_sqlpara>{7}</to_sqlpara> <!--column_name-->
<to_sqlpara>{tasktime}</to_sqlpara>
</action>     

  

非常的简单