快捷搜索:

oracle存储过程导出索引

oracle存储过程导出索引   1、首先要建立一个directory  create or replace directory DIR_DUMP as 'd:/918博天堂网址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)||' (';      loopAAAsp;        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.bkjia.com/oracle/481216.htmlwww.bkjia.comtruehttp://www.bkjia.com/oracle/481216.htmlTechArticleoracle存储过程导出索引 1、首先要建立一个directory create or replace directory DIR_DUMP as d:/index; 2、执行过程,在路径d:/index 下有index.txt文件为导出...

本文源自: AG88平台

您可能还会对下面的文章感兴趣: