oracle用存储过程导出表结构和索引

在网上搜索了一圈，没有看到同时导出表结构和索引的sp。还好，有单独的导出表结构和索引的程序，偶把他俩合并到了一起，工作正常，呵呵。如果需要，也可以单独拆分出来，我用的是oracle10g

 

create or replace procedure sp_exp_tableframe(v_partition_status varchar2 default 'Y')
 is
  type column_type is table of  user_tab_columns.column_name%type;
  v_column column_type;
  type data_type is table of  user_tab_columns.data_type%type;
  v_type data_type;
  type length_type is table of  user_tab_columns.data_length%type;
  v_length length_type;
  type datapre_type is table of  user_tab_columns.DATA_PRECISION%type;
  v_ldatapre datapre_type;
  type datasca_type is table of  user_tab_columns.DATA_SCALE%type;
  v_dayasca datasca_type;
  v_str clob;
  file_name UTL_FILE.file_type;
  v_tables varchar2(50);
  partition_status varchar2(3);
  partition_keywords varchar2(30);
  TYPE part_cursor is ref CURSOR;
  part_name part_cursor;
  partition_name user_tab_partitions.partition_name%type;
  high_value user_tab_partitions.high_value%type;
begin
  file_name := UTL_FILE.FOPEN('DIR_DATATXT','table.txt','w');
  --判断是否需要分区
  partition_status := v_partition_status;
  --按表循环
  for j in (select table_name  from user_tables  group by table_name ) loop
  v_tables :=upper(j.table_name);
  v_str := 'create table '||v_tables||'(';
  UTL_FILE.PUT_LINE(file_name,v_str);
  --提取表的字段信息
  select column_name,data_type,data_length,DATA_PRECISION,DATA_SCALE
    bulk collect into v_column,v_type,v_length,v_ldatapre,v_dayasca
      from user_tab_columns where table_name=v_tables;
    --按字段循环
    for i in 1..v_column.count loop
      if v_type(i)= 'DATE' or v_type(i) like 'TIMESTAMP%'  then
        v_str :=v_column(i)||' '||v_type(i)||',';
      elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is not null  then
        v_str :=v_column(i)||' '||v_type(i)||'('||v_ldatapre(i)||','||v_dayasca(i)||'),';
      elsif v_type(i)= 'NUMBER' and v_ldatapre(i) is  null  then
        v_str :=v_column(i)||' '||v_type(i)||',';
      else
        v_str :=v_column(i)||' '||v_type(i)||'('||v_length(i)||'),';
      end if;   
      if i=v_column.count then
        v_str :=substr(v_str,1,length(v_str)-1);
      end if;
      UTL_FILE.PUT_LINE(file_name,v_str);     
    end loop;
    --判断是否添加分区
    if partition_status = 'Y' then         
    SELECT nvl(max(column_name),'0') into partition_keywords  FROM USER_PART_KEY_COLUMNS
      where object_type = 'TABLE'  and name=v_tables;
      if partition_keywords != '0' then
        UTL_FILE.PUT_LINE(file_name,')partition by range ('||partition_keywords||')(');     
          open part_name for select partition_name,high_value  from user_tab_partitions
          where table_name = v_tables;
        v_str := null;
        loop
          fetch part_name into partition_name,high_value;           
          if part_name%notfound then
            --去掉最后逗号
            v_str :=substr(v_str,1,length(v_str)-1);
            UTL_FILE.PUT_LINE(file_name,v_str);
            exit;
          end if;         
          UTL_FILE.PUT_LINE(file_name,v_str);
          v_str :='partition '||partition_name||' values less than ('||high_value||'),';
          end loop;
      end if;
    end if;
    UTL_FILE.PUT_LINE(file_name,');');
    UTL_FILE.PUT_LINE(file_name,'-------------------------------------------------------------');
    end loop; 
    UTL_FILE.fclose_all;
end sp_exp_tableframe;

 

有一点不太完美,没有导出建表时表空间及初始大小.希望有人能指点,呵呵。