Display partition information for a specified index

Displays partition information for the specified index, or all indexes.

SET LINESIZE 500
SET PAGESIZE 1000
SET FEEDBACK OFF
SET VERIFY OFFSELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM   dba_ind_partitions a
WHERE  a.index_name  = Decode(‘BRANCH_STATS_IX’,'ALL’,a.index_name,’BRANCH_STATS_IX’)
ORDER BY a.index_name, a.partition_name;

Replace ‘BRANCH_STATS_IX’ with valid index_name

select ‘Alter Index ‘|| index_owner ||’.'||index_name
||’ Rebuild Partition ‘ || partition_name ||’  Online;
‘ from dba_ind_partitions a
WHERE  a.index_name  = Decode(‘BRANCH_STATS_IX’,'ALL’,a.index_name,’BRANCH_STATS_IX’)
ORDER BY a.index_name, a.partition_name

This should create script like …
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060201 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060202 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060203 Online;
Alter Index STATS.BRANCH_STATS_IX Rebuild Partition BS_20060204 Online;

Problem : Oracle reported block corruption on data file id 21,22 mapped for ABC,XYZ tablespaces.

Get list of all partitions mapped into corrupt tablespaces

SELECT a.index_name,
a.partition_name,
a.tablespace_name,
a.initial_extent,
a.next_extent,
a.pct_increase,
a.num_rows
FROM dba_ind_partitions a
WHERE a.tablespace_name in (‘ABC’,'XYZ’)
ORDER BY a.index_name, a.partition_name

Get a script to rebuild all these partitions

SELECT ‘Alter Index ‘|| index_owner ||’.'||index_name ||’ Rebuild Partition ‘ || partition_name ||’ Online; ‘
FROM dba_ind_partitions a
WHERE a.tablespace_name in (‘ABC,’XYZ)
ORDER BY a.index_name, a.partition_name;