Generate index DDL automatically

• Eric Mao
• Published: 24 Nov 2003

If you need to drop and recreate indexes, you may find this script useful. It is designed to generate current index DDL for a specific schema. It can be a backup of an index definition before or after applying a change. Also, in the generated scripts, you can modify index parameters to meet your demand, like space allocation, change tablespaces, etc. This script is for B*-tree and bitmap indexes. If you have other index type, such as IOT, text index, or partitioned index, you have to update it accordingly. Also you can add more parameters for index DDL if you have non-default values. Here I just specify initial_extent and next_extent. It was tested on Oracle 8i and 9i.

--
CREATE OR REPLACE PROCEDURE INDEX_DDL_GENERATOR(P_USERNAME IN VARCHAR2) IS
-------------------------------------------------
-- To auto-generate indexes DDL
--
-- emao Nov 14,2003
--
--------------------------------------------------

CURSOR C_INDEX IS
SELECT 'CREATE '||DECODE(INDEX_TYPE,'NORMAL',UNIQUENESS, INDEX_TYPE)||'
   INDEX '||OWNER||'.'||INDEX_NAME||' ON '||OWNER||'.'||TABLE_NAME I_HEADER,
   'TABLESPACE '||TABLESPACE_NAME||' STORAGE ( INITIAL '|| INITIAL_EXTENT/1024
   || 'K NEXT '|| NEXT_EXTENT/1024 ||'K );'  
I_TAIL, TABLE_NAME, INDEX_NAME
FROM SYS.DBA_INDEXES
WHERE OWNER= P_USERNAME
AND INDEX_TYPE IN ('NORMAL','BITMAP');

R_INDEX C_INDEX%ROWTYPE;

CURSOR C_IND_COL(P_TABLE_NAME VARCHAR2, P_INDEX_NAME VARCHAR2) IS
SELECT COLUMN_NAME||' '||DESCEND COLUMN_NAME, COLUMN_POSITION
FROM SYS.DBA_IND_COLUMNS
WHERE INDEX_OWNER=P_USERNAME
AND TABLE_NAME=P_TABLE_NAME
AND INDEX_NAME=P_INDEX_NAME
ORDER BY COLUMN_POSITION;

R_IND_COL  C_IND_COL%ROWTYPE;

V_COL_STR VARCHAR2(1024);

BEGIN

OPEN C_INDEX;
LOOP
    FETCH C_INDEX INTO R_INDEX;
    EXIT WHEN C_INDEX%NOTFOUND;

    V_COL_STR := ' ';
    OPEN C_IND_COL(R_INDEX.TABLE_NAME,R_INDEX.INDEX_NAME);
    LOOP

      FETCH C_IND_COL INTO R_IND_COL;
      EXIT WHEN C_IND_COL%NOTFOUND;
      IF C_IND_COL%ROWCOUNT = 1 THEN
         V_COL_STR := R_IND_COL.COLUMN_NAME;
      ELSE
         V_COL_STR := V_COL_STR||', '||R_IND_COL.COLUMN_NAME;
      END IF ;
      --DBMS_OUTPUT.PUT_LINE(V_COL_STR);

    END LOOP;
    CLOSE C_IND_COL;

    DBMS_OUTPUT.PUT_LINE( R_INDEX.I_HEADER||'( '||V_COL_STR||' )'||CHR(10)||R_INDEX.I_TAIL);
    DBMS_OUTPUT.PUT_LINE(CHR(10));
END LOOP;
CLOSE C_INDEX;

END;
/


SET SERVEROUT ON SIZE 999999
SPOOL INDEX_DDL.SQL
EXEC INDEX_DDL_GENERATOR('&schema_name');
SPOOL OFF

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.