CREATE TABLE TEMP_INDEX_NAMES
(
  INDEXNAME   VARCHAR2(500 CHAR)
);


cat index_rebuild_AUTO_2.sql
conn vinod/vinod123
-- select only those indexes with an estimated space saving percent greater than 25%
VAR savings_percent NUMBER;
EXEC :savings_percent := 20;
-- select only indexes with current size (as per cbo stats) greater then 1MB
VAR minimum_size_mb NUMBER;
EXEC :minimum_size_mb := 1;
SET SERVEROUT ON ECHO OFF FEED OFF VER OFF TAB OFF LINES 300;
COL report_date NEW_V report_date;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD"T"HH24:MI:SS') report_date FROM DUAL;
SPO /tmp/indexes_2b_shrunk_&&report_date..txt;
DECLARE
l_used_bytes NUMBER;
l_alloc_bytes NUMBER;
l_percent NUMBER;
V_INDEXNAME VARCHAR2(100);
v_cnt NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE('PDB: '||SYS_CONTEXT('USERENV', 'CON_NAME'));
DBMS_OUTPUT.PUT_LINE('---');
DELETE from TEMP_INDEX_NAMES;
FOR i IN (SELECT x.owner,x.table_name, x.index_name, SUM(s.leaf_blocks) * TO_NUMBER(p.value) index_size,
REPLACE(DBMS_METADATA.GET_DDL('INDEX',x.index_name,x.owner),CHR(10),CHR(32)) ddl
FROM dba_ind_statistics s, dba_indexes x, dba_users u, v$parameter p
WHERE u.oracle_maintained = 'N'
AND x.owner = u.username
AND x.tablespace_name NOT IN ('SYSTEM','SYSAUX')
AND x.index_type LIKE '%NORMAL%'
AND x.table_type = 'TABLE'
AND x.status = 'VALID'
AND x.temporary = 'N'
AND x.dropped = 'NO'
AND x.visibility = 'VISIBLE'
AND x.segment_created = 'YES'
AND x.orphaned_entries = 'NO'
AND p.name = 'db_block_size'
AND s.owner = x.owner
AND s.index_name = x.index_name
GROUP BY
x.owner, x.table_name,x.index_name, p.value
HAVING
SUM(s.leaf_blocks) * TO_NUMBER(p.value) > :minimum_size_mb * POWER(2,20)
ORDER BY
index_size DESC)
LOOP
DBMS_SPACE.CREATE_INDEX_COST(i.ddl,l_used_bytes,l_alloc_bytes);
IF i.index_size * (100 - :savings_percent) / 100 > l_alloc_bytes THEN
l_percent := 100 * (i.index_size - l_alloc_bytes) / i.index_size;
                        V_INDEXNAME := RPAD(i.owner||'.'||i.index_name, 50);
                           DBMS_OUTPUT.PUT_LINE ('V_INDEXNAME    : '||V_INDEXNAME);
                                                   BEGIN
                                                 INSERT INTO TEMP_INDEX_NAMES (INDEXNAME) VALUES (V_INDEXNAME);
                                                        END;
END IF;
END LOOP;
BEGIN
                SELECT count(*) into v_cnt
                  FROM TEMP_INDEX_NAMES;
                        IF v_cnt > 0
                        THEN
                         FOR o_indexbuild IN (SELECT *
                                          FROM TEMP_INDEX_NAMES)
                                  LOOP
                                                                BEGIN
                                                                          EXECUTE IMMEDIATE 
                                                                                        'alter index '
                                                                                 || o_indexbuild.INDEXNAME
                                                                                 || '   rebuild online';
                                                                                  COMMIT;
                                                                   EXCEPTION
                                                                          WHEN OTHERS
                                                                          THEN
                                                                                 DBMS_OUTPUT.PUT_LINE ('Error while rebuild index..'||o_indexbuild.INDEXNAME );
                                                                   END;
                                  END LOOP;                      
                END IF;
        END;
END;
/