科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道导出Oracle里创建非唯一索引脚本的方法

导出Oracle里创建非唯一索引脚本的方法

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

  在ORACLE里用逻辑备份工具exp导出数据时,如果使用默认参数, 会把索引一起导出来。当数据和索引小的时候,我们可能不太会计较导入时间; 如果数据和索引大的时候,就应该考虑导入时间的问题了。      实际地说。

作者:中国IT实验室 来源:中国IT实验室 2007年10月4日

关键字: 备份 数据库 ORACLE

  • 评论
  • 分享微博
  • 分享邮件

  在ORACLE里用逻辑备份工具exp导出数据时,如果使用默认参数, 会把索引一起导出来。当数据和索引小的时候,我们可能不太会计较导入时间; 如果数据和索引大的时候,就应该考虑导入时间的问题了。
  
  实际地说,二进制dmp备份文件里有些索引对备份是用处不大的, 导出时完全可以选择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_ownernot null varchar2(30)
   table_name not null varchar2(30)
   column_name   varchar2(4000)
   column_positionnot 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;
  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; 
  

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件