oracle 存储过程导出索引

bzj6iheq6v

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;