This script generates DDL for all the indexes associated with a given table. It captures only the most basic index metadata; it doesn't support partitioning, IOT, etc.
It captures the following index metadata only: Index name, columns & positions, uniqueness, index tablespaces.
This script is meant as a time-saver, not a replacement for more thorough methods.
You can capture the basic DDL for the base table here.
Note: In 9i and above, you can use the PL/SQL supplied package DBMS_METADATA to extract DDL, instead of using scripts like this one.
declare
my_owner varchar2(30) := UPPER('&owner');
my_name varchar2(30) := UPPER('&tabname');
cursor colcur (ind_in varchar2)
is
select * from dba_ind_columns
where table_owner=my_owner and table_name=my_name
and index_name=ind_in
order by column_position;
cursor indcur is
select * from dba_indEXES
where table_owner=my_owner and table_name=my_name;
createstring varchar2(4000);
begin
for indrow in indcur loop
createstring := 'CREATE';
if indrow.uniqueness = 'UNIQUE' then
createstring := createstring || ' UNIQUE';
end if;
createstring := createstring || ' INDEX ' || indrow.index_name || ' ON '
|| indrow.table_name || ' (';
for colrow in colcur (indrow.index_name) loop
createstring := createstring || colrow.column_name || ',';
end loop; --colcur
createstring := rtrim(createstring,',');
createstring := createstring || ') ';
createstring := createstring || ' TABLESPACE ' || indrow.tablespace_name || ';';
dbms_output.put_line ( createstring );
dbms_output.put_line (' ' );
end loop; --indcur
end;