Question: I wish to generate the
create index DDL statements from my data dictionary. What script will
generate the create index syntax?
Answer: The
content for create index statement is kept in the dba_indexes and
dba_objects views, and you can download pre-tested scripts to generate
create statement from the
Oracle script
download.
The easiest way to punch create index DDL syntax
is using the
dbms_metadata
package, something like this:
select
dbms_metadata.get_ddl('INDEX',index_name)
from
user_indexes
where
xxxx;
Another approach is to write a PL/SQL procedure
to generate all create index statement for a schema:
declare
s varchar2(4000);
begin
for c1 in (
select dbms_metadata.get_ddl(object_type=>'INDEX',name=>cur.index_name)
from user_indexes)
loop
s
:= dbms_metadata.get_ddl(object_type=>'INDEX',name=>c1.index_name);
end loop;
dbms_output.put_line('S= '||s);
end;
Here are additional details on using the
create index statement.
In sum, if you don't want to
write your own dictionary scripts, I recommend the
Oracle script
download.
For more information on creating indexes see these articles:
