在线咨询
QQ咨询
服务热线
服务热线:13125520620
TOP

导出创建非唯一索引脚本的方法-数据库

发布时间:2011-11-12 浏览:4934

  在ORACLE里用逻辑备份工具exp导出数据时,如果使用默认参数, 会把创建索引的语句一起导出来。当数据和索引小的时候,

  我们可能不太会计较导入时间; 如果数据和索引大的时候,就应该考虑导入时间的问题了。
  
  如果在导出时选择 indexes=n 的参数, 索引类型是非唯一(nounique)要根据ORACLE数据字典dba_indexes和

  dba_ind_columns里的信息生成创建索引的脚本。在导入完成后再根据需要运行这些创建索引的脚本。

  dba_indexes里记录了索引类型和存储参数等信息。
   
  dba_ind_columns里记录了索引的字段信息, 它的结构如下: 

SQL> desc dba_ind_columns;
name                                   null?    type
----------------------------------------- -------- -------------------------
index_owner                            not null         varchar2(30)
index_name                             not null         varchar2(30)
table_owner                            not null         varchar2(30)
table_name                             not null         varchar2(30)
column_name                                                  varchar2(4000)
column_position                        not null         number
column_length                          not null         number
descend                                                varchar2(4)

  column_name记录着有索引的字段, column_position标记着字段在创建索引时的位置, descend指索引的排序, 有asc和desc

  两种,而desc排序方法用的较少,本文只考虑asc的情况。

  步骤一:先创建一个视图index_nouniq_column_num列出非系统用户nonunique索引的用户名, 索引名和字段数量。

SQL> create view index_nouniq_column_num as select t1.owner,t1.index_name,count(0) as column_num
        from dba_indexes t1,dba_ind_columns t2 where t1.uniqueness='NONUNIQUE'
        and instr(t1.owner,'sys')=0 and t1.owner=t2.index_owner and t1.index_name=t2.index_name
        group by t1.owner,t1.index_name order by t1.owner,column_num;

  步骤二:为了处理方便,建一个索引字段临时表index_columns, 它的column_names记录了以逗号分隔,顺序排列的索引字段。

SQL> create table index_columns(
        index_owner                varchar2(30)        not null,
        index_name                varchar2(30)        not null,
        column_names                varchar2(512)        not null)
        tablespace users;

  步骤三:把只有一个字段的索引内容插入索引字段临时表index_columns。

SQL> insert into index_columns select t1.owner,t1.index_name,t2.column_name from
        index_nouniq_column_num t1,dba_ind_columns t2
        where t1.column_num=1 and t1.owner=t2.index_owner and t1.index_name=t2.index_name
        order by t1.owner,t1.index_name;
SQL> commit;

  步骤四:把多个字段的索引内容插入索引字段临时表index_columns。
   
  用到以下一个函数getcloumns和过程select_index_columns。

  函数getcloumns:
create or replace function getcloumns(
        index_owner1         in varchar2,
        index_name1            in varchar2,
        column_nums1        in number) return varchar2
is
     all_columns         varchar2(512);
     total_num                number;
     i                         number;
     cursor c1 is select column_name from dba_ind_columns where index_owner=index_owner1 and
      index_name=index_name1 order by column_position;
     dummy c1%rowtype;
begin
   total_num:=column_nums1;
   open c1;
   fetch  c1  into   dummy;
   i:=0;
   while c1%found  loop
           i:=i+1;
           if (i=total_num) then
                   all_columns:= all_columns||dummy.column_name;
           else
                   all_columns:= all_columns||dummy.column_name||',';
           end if;
           fetch c1 into dummy;
   end loop;
   close c1;
   return all_columns;
exception
    when no_data_found then
           return all_columns;
end;
/

  过程select_index_columns:
create or replace procedure select_index_columns
is
   all_columns         varchar2(2000);
   cursor c1 is select * from index_nouniq_column_num where column_num>=2;
   dummy c1%rowtype;
begin
   open c1;
   fetch  c1  into   dummy;
   while c1%found  loop
              select getcloumns(dummy.owner,dummy.index_name,dummy.column_num) into all_columns from dual;
              insert into index_columns values(dummy.owner,dummy.index_name,all_columns);
           fetch c1 into dummy;
   end loop;
   commit;
   close c1;
exception
   when others then
           rollback;
end;
/

SQL> exec select_index_columns;

  执行select_index_columns过程就可以把多个字段的索引内容插入索引字段临时表了。

  步骤五:最后运行create_now_index.sql,根据索引字段临时表index_columns和dba_indexes在路径/oracle_backup/log

  下生成创建非唯一索引脚本create_index.sql。

create_now_index.sql内容:

set heading off;
set pagesize 5000;
truncate table index_columns;

  -- 把多个字段的索引内容插入索引字段临时表

exec select_index_columns;

  -- 把只有一个字段的索引内容插入索引字段临时表

insert into index_columns select t1.owner,t1.index_name,t2.column_name
        from index_nouniq_column_num t1,dba_ind_columns t2
        where t1.column_num=1 and t1.owner=t2.index_owner and t1.index_name=t2.index_name
        order by t1.owner,t1.index_name;
commit;
spool /oracle_backup/log/create_index.sql;
SELECT 'CREATE INDEX '||t1.owner||'.'||t1.index_name|| chr(10) ||' ON '||t1.table_name||
        ' ('||column_names||')'|| chr(10) ||' TABLESPACE '||t1.tablespace_name|| chr(10) ||
        ' PCTFREE '||t1.pct_free || chr(10) ||' STORAGE (INITIAL '||t1.initial_extent||
        ' NEXT '||t1.next_extent||' PCTINCREASE '||t1.pct_increase||');'|| chr(10) || chr(10)
FROM dba_indexes t1,index_columns t2
WHERE t1.owner=t2.index_owner and t1.index_name=t2.index_name
ORDER BY t1.owner, t1.table_name;
spool off;

TAG
软件定制,软件开发,瀚森HANSEN,辽宁,沈阳,抚顺
0
该内容对我有帮助