oracle存储过程导出索引


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;