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;

http://www.dengb.com/oracle/481216.htmlwww.dengb.comtruehttp://www.dengb.com/oracle/481216.htmlTechArticleoracle存储过程导出索引 1、首先要建立一个directory create or replace directory DIR_DUMP as d:/index; 2、执行过程，在路径d:/index 下有index.txt文件为导出...