A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

Index and Stats Gather Scripts

3 min read

Check for index owned by sys when table owned by SCOTT

select count (*) from dba_indexes
where TABLE_NAME in
(select object_name from dba_objects where OBJECT_TYPE in ('TABLE') and owner = 'SCOTT')
and owner ='SYS';

–View problem indexes, verify table_owner is SCOTT (if INDEX_OWNER and TABLE_OWNER are sys, then you have 2 tables with 2 owners, but same table name, see steps below)

col OWNER for a30
col INDEX_NAME for a30
col TABLE_NAME for a30
col TABLE_OWNER for a30
select OWNER,INDEX_NAME,TABLE_NAME,TABLE_OWNER from dba_indexes
where TABLE_NAME in
(select object_name from dba_objects where OBJECT_TYPE in ('TABLE') and owner = 'SCOTT')
and owner ='SYS';
INDEX_OWNER INDEX_NAME TABLE_NAME TABLE_OWNER

–get the columns for the index and its tablespace

COL COLUMN_NAME format a30
break on index_name skip 1
SELECT DISTINCT a.index_name,a.COLUMN_NAME,b.TABLESPACE_NAME
from dba_ind_columns a,dba_indexes b
where a.table_name=b.table_name
and a.INDEX_NAME='SCT_CRN'
order by COLUMN_NAME;
INDEX_NAME COLUMN_NAME TABLESPACE_NAME

— Get size of table, Each GB will take about 1 minute to create the index on.

select segment_name,segment_type,bytes/1024/1024/1024 GB
from dba_segments
where segment_type='TABLE' and segment_name='SCT_CRN';
SEGMENT_NAME SEGMENT_TYPE GB
SCT_REF TABLE .002134

–create index INVISIBLE

CREATE INDEX SCOTT.SCT_CRN ON SCT_REF(CATALOG_CD) ONLINE INVISIBLE TABLESPACE Tab01;

–Check stats on Index :

select * from dba_ind_statistics where index_name= 'SCT_CRN';

–If the stats are not present for the index, you will need to create them using the below commands:
–Export the pending statistics to the statistics table:

exec DBMS_STATS.EXPORT_PENDING_STATS(OWNNAME=> 'SCOTT',TABNAME=>'TABLE_01',STATTAB=>'STAT_TABLE',STATID=>'STAT_ID',STATOWN=>'SCOTT');

–Verify you have pending statistics for the index in the statistics table :

Select count(*) from STAT_TABLE where statid = 'STAT_ID' and type = 'I' and c1 = 'SCT_CRN';

–Remove extra stats :

delete from STAT_TABLE where statid = 'STAT_ID' and c1 != 'SCT_CRN';
commit;

–Set publish preference to true for the table :

exec dbms_stats.set_table_prefs('SCOTT','TABLE_01','PUBLISH','TRUE');

–Publish the statistics for the index :

exec dbms_stats.import_index_stats(statid=>'STAT_ID',stattab=>'STAT_TABLE',statown=>'SCOTT',indname=>'SCT_CRN',ownname=>'SCOTT');

–Set the publish preference back to false :

exec dbms_stats.set_table_prefs('SCOTT','TABLE_01','PUBLISH','FALSE');

–Clear out the stat table :

delete from STAT_TABLE where statid = 'STAT_ID' and c1 = 'SCT_CRN';
commit;

–Check that index stats are now present :

select * from dba_ind_statistics where index_name= 'SCT_CRN';

–Once the stats for the index are present, then modify the index to visible:

alter index SCOTT.SCT_CRN visible;

— drop temp index

drop index SCOTT.SCT_CRN_TEMP;

2 tables with 2 owners, but same table name
Work with client team to determine which table needs to be dropped.

col object_name for a30
select object_name,OBJECT_TYPE,owner from dba_objects
where OBJECT_TYPE ='TABLE'
and object_name in (
      select object_name from (
            select object_name, count(*) as obj_count from dba_objects
            where OBJECT_TYPE in ('TABLE','INDEX')
            group by object_name,OBJECT_TYPE)
            where obj_count > 1
            and object_name not like '%$%'
                        )
and object_name in
(select object_name from dba_objects where OBJECT_TYPE ='TABLE' and owner = 'SCOTT')
order by 1;
OBJECT_NAME OBJECT_TYPE OWNER