Create DDL / DML for INDEX or TABLE or anything!!!

 

Want to get all of the DML / DDL for indexes or tables?  Here is the INDEX example below.  Just remove more from the end WHERE clause to have more DML. 

SELECT 'SELECT DBMS_METADATA.GET_DDL(''' || 'INDEX''' || ',''' || index_name || '''' || ',''' || 'PD812' || ''')'  || ' FROM dual ;'
FROM all_indexes where owner = 'PRODDTA' and table_name = 'F0911'

Spool the results to a file and add the following to the header of the file:

set heading off
set feedback off
set long 99999
set pages 0
set heading off
set lines 1000
set wrap on
set linesize 100
spool c:\temp\F0911DML.sql

SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_11','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_12','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_13','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_15','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_17','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_18','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_19','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_23','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_24','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_25','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_27','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_28','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_29','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_30','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_32','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_33','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_34','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_35','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_36','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_37','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_0','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_6','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_8','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_9','PRODDTA') FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0911_10','PRODDTA') FROM dual ;
spool off;

Wow, run the script above and you will have all of the “CREATE INDEX” statements.  Note that the initial extents might not be too great (mine were all standard).