Generate index DDL automatically
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.
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.