Index and Stats Gather Scripts
3 min readCheck 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 |
