oracle 存储过程导出索引

1、首先要建立一个directory
create or replace directory DIR_DUMP as 'd:/index';

2、执行过程，在路径'd:/index' 下有index.txt文件为导出所有索引的创建语句。

create or replace procedure index_frame
is
type index_name_type is table of user_indexes.index_name%type;
v_index_name index_name_type;
type index_type_type is table of user_indexes.index_type%type;
v_index_type index_type_type;
type table_name_type is table of user_indexes.table_name%type;
v_table_name table_name_type;
type ind_post_cur is ref cursor;
ind_post ind_post_cur;
v_column_name user_ind_columns.COLUMN_NAME%type;
v_column_position user_ind_columns.column_position%type;
frame_name UTL_FILE.file_type;
v_str clob;
s_index_type user_indexes.index_type%type;
status number;
begin
frame_name := UTL_FILE.fopen('DIR_DUMP','index.txt','w');
select index_name,index_type,table_name bulk collect into v_index_name,v_index_type,v_table_name
from user_indexes where index_name not like '%BIN$%';
for i in 1..v_index_name.count loop
status :=1;
if v_index_type(i) = 'NORMAL' then
s_index_type := '';
else s_index_type := v_index_type(i);
end if;
open ind_post for select column_name,column_position from user_ind_columns
where index_name=upper(v_index_name(i))
order by column_position asc;
v_str := 'create '||s_index_type||' index '||v_index_name(i)||' on '||v_table_name(i)||' (';
loop
fetch ind_post into v_column_name,v_column_position;
if ind_post%notfound then
v_str := v_str||') ;';
exit;
elsif status =1 then
v_str := v_str||v_column_name;
status := 0 ;
else
v_str := v_str||','||v_column_name;
end if;
end loop;
UTL_FILE.put_line(frame_name,v_str);
close ind_post;
end loop;
UTL_FILE.fclose_all;
end;