Generate missing indexes in 1 environment from another–oracle

I’ve done a heap of tuning in production, created a bunch of indexes and I’m pretty happy with how it looks.  Remember that you only need to create the indexes in the database if they are for tuning – they don’t need to be added to the table specs in JDE.

So, how do I easily generate all of the DDL for these indexes and create them in other locations?

I’’’ generate the create index statements while reconciling

select 'SELECT DBMS_METADATA.GET_DDL(''INDEX'',''' || index_name || ''',''' || OWNER || ''') ||'';'' FROM dual ;'
from all_indexes t1 where t1.owner = 'CRPDTA' and not exists (select 1 from all_indexes t2 where t2.owner = 'TESTDTA' and t1.index_name = t2.index_name) ;

Which will give you a bunch of results like this:

SELECT DBMS_METADATA.GET_DDL('INDEX','F0902_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F41001_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F3413_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F03B11_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F03012Z1_ORA1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F03012Z1_ORA0','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F01151_ORA0','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F5646_SRM1','CRPDTA') ||';' FROM dual ;
SELECT DBMS_METADATA.GET_DDL('INDEX','F0414_9SRM','CRPDTA') ||';' FROM dual ;

So whack some headers on this to trim the output:

set heading off
set feedback off
set long 99999
set pages 0
set heading off
set lines 1000
set wrap on

And use the run script button in SQL Developer:

You’ll get a pile of output like this:

CREATE INDEX "CRPDTA"."F01151_ORA0" ON "CRPDTA"."F01151" ("EAAN8", "EAIDLN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CRPINDEX2"
PARALLEL ;

CREATE INDEX "CRPDTA"."F5646_SRM1" ON "CRPDTA"."F5646" ("ALDOCO", "ALLNID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CRPINDEX2"
PARALLEL ;

You can then change the tablespace and owner information and run in your other environments.