select index_name, num_rows / leaf_blocks as lpb
from user_indexes
where leaf_blocks > 0
and num_rows > 5000
and INDEX_TYPE = 'NORMAL'
and table_name = 'PD_CST_CNTR'
and index_name like '%RID%'
order by 2 desc
/

INDEX_NAME                            LPB
------------------------------ ----------
PD_CCNTR_RID6_IX                  650.125
PD_CCNTR_RID7_IX               266.692308
PD_CCNTR_RID1_IX                   260.05
PD_CCNTR_RID2_IX               253.682927
PD_CCNTR_RID3_IX               241.883721

INDEX_NAME                            LPB
------------------------------ ----------
PD_CCNTR_RID1_IX                  650.125
PD_CCNTR_RID6_IX                  650.125
PD_CCNTR_RID7_IX               266.692308
PD_CCNTR_RID2_IX               253.682927
PD_CCNTR_RID3_IX               241.883721

PROCEDURE rebuild_fragmented_indexes (
        pTab            IN USER_TABLES.TABLE_NAME%TYPE
,       pPctUsed        IN NUMBER := 50
) AS
        lSqlStmt        VARCHAR2(2000);
        lRowSize        NUMBER;

        lClientInfo     VARCHAR2(2000) := pTab || ' ' || to_char(pPctUsed);
        lDbBlockSize    NUMBER;
BEGIN
        dbms_application_info.set_module('rebuild_fagmented_indexes', NULL);
        dbms_application_info.set_client_info(lClientInfo);

        SELECT  to_number(value)
        INTO    lDbBlockSize
        FROM    v$parameter
        WHERE   name = 'db_block_size';

        --
        -- Fetch all indexes for tables matching pTab (with >5000 rows)
        -- For partitioned indexes, select the partitions
        --
        FOR ind IN (
                SELECT  i.table_name
                ,       i.index_name
                ,       NULL as partition_name
                ,       i.num_rows / i.leaf_blocks AS rows_per_block
                FROM    user_indexes i
                LEFT OUTER JOIN user_part_indexes p
                ON      p.index_name = i.index_name
                WHERE   p.index_name IS NULL
                AND     i.num_rows >= 5000
                AND     i.leaf_blocks > 0
                AND     i.table_name LIKE pTab
                UNION ALL
                SELECT  i.table_name
                ,       p.index_name
                ,       p.partition_name
                ,       p.num_rows / p.leaf_blocks AS rows_per_block
                FROM    user_ind_partitions p
                JOIN    user_indexes i
                ON      i.index_name = p.index_name
                WHERE   p.num_rows >= 5000
                AND     p.leaf_blocks > 0
                AND     i.table_name LIKE pTab
        ) LOOP
                --
                -- Build a SQL statement that selects the average size of an index
                -- entry. Since every index entry contains a ROWID, add 10 to the
                -- total. SQL is of the form
                -- SELECT AVG(10 + NVL(VSIZE(col1),0) + NVL(VSIZE(col2),0)) + ...)
                -- FROM pTab
                --
                lSqlStmt := 'SELECT avg(10';
                FOR col IN (
                        SELECT  column_name
                        FROM    user_ind_columns
                        WHERE   index_name = ind.index_name
                ) LOOP
                        lSqlStmt := lSqlStmt || '+nvl(vsize(' || col.column_name || '),0)';
                END LOOP;

                lSqlStmt := lSqlStmt || ')FROM ' || ind.table_name;
                EXECUTE IMMEDIATE lSqlStmt INTO lRowSize;

                --
                -- The current number of rows per leaf block times the logical average
                -- row size (in bytes) gives a minimum space used in each leaf block
                -- by data (there are other overheads not counted here). Take this space
                -- as a percentage of the block size. If it falls below pPctUsed then
                -- rebuild and analyze the index
                --
                IF (100 * ind.rows_per_block * lRowSize / lDbBlockSize < pPctUsed) THEN
                        lSqlStmt := 'ALTER INDEX ' || ind.index_name || ' REBUILD ';

                        IF ind.partition_name IS NOT NULL THEN
                                lSqlStmt := lSqlStmt || 'PARTITION ' || ind.partition_name;
                        END IF;

                        dbms_application_info.set_module(
                                'rebuild_fagmented_indexes'
                        ,       'Rebuild ' || ind.index_name || ' ' || ind.partition_name);
                        dbms_application_info.set_client_info(lClientInfo);

                        EXECUTE IMMEDIATE lSqlStmt;

                        dbms_application_info.set_module(
                                'rebuild_fagmented_indexes'
                        ,       'Gather stats ' || ind.index_name || ' ' || ind.partition_name);
                        dbms_application_info.set_client_info(lClientInfo);

                        dbms_stats.gather_index_stats(
                                user
                        ,       ind.index_name
                        ,       ind.partition_name
                        );
                END IF;
        END LOOP;
        dbms_application_info.set_client_info(NULL);
        dbms_application_info.set_module(NULL, NULL);
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                dbms_application_info.set_client_info(NULL);
                dbms_application_info.set_module(NULL, NULL);
                raise_application_error(-20000, sqlerrm);
END rebuild_fragmented_indexes;