ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL提取当前库内索引的创建语句

SQL提取当前库内索引的创建语句

原创 Linux操作系统 作者:qqmengxue 时间:2009-10-15 22:12:34 0 删除 编辑

今天数据库需要将几个用户里的表索引以某一个用户为标准进行一次统一版本,于是整理了一个脚本方便以后使用,其中涉及到当前表里的索引创建语句、LOB索引的表空间移动,笔记一下。。。

 

select 'create index '||index_name||' on '||table_name||'('||researchlist||') tablespace  '||tablespace_name||' nologging;' from (
select table_name,tablespace_name,index_name,translate(ltrim(text,'/'),'*/','*,') researchlist from (
select row_number() over(partition by tablespace_name,index_name,table_name order by column_position desc) nums,text,index_name,tablespace_name,table_name from (
select index_name,table_name,column_name,tablespace_name,column_position, sys_connect_by_path(column_name,'/') text from (
select t1.index_name,
       t1.table_name,
       t1.column_name||' '||decode(t1.descend,'DESC',t1.descend) column_name,
       t.tablespace_name,
       t1.column_position
  from user_indexes t
  left join user_ind_columns t1 on t.index_name = t1.index_name
 where t.index_type in( 'NORMAL','FUNCTION-BASED NORMAL')
   AND T.UNIQUENESS = 'NONUNIQUE'
   )
connect by index_name = prior index_name and column_position -1 = prior column_position)where index_name is not null) where nums=1)
union
select 'create bitmap index '||index_name||' on '||table_name||'('||researchlist||') tablespace  '||tablespace_name||' nologging;' from (
select table_name,tablespace_name,index_name,translate(ltrim(text,'/'),'*/','*,') researchlist from (
select row_number() over(partition by tablespace_name,index_name,table_name order by column_position desc) nums,text,index_name,tablespace_name,table_name from (
select index_name,table_name,column_name,tablespace_name,column_position, sys_connect_by_path(column_name,'/') text from (
select t1.index_name,
       t1.table_name,
       t1.column_name||' '||decode(t1.descend,'DESC',t1.descend) column_name,
       t.tablespace_name,
       t1.column_position
  from user_indexes t
  left join user_ind_columns t1 on t.index_name = t1.index_name
 where t.index_type in( 'BITMAP')
   AND T.UNIQUENESS = 'NONUNIQUE'
   )
connect by index_name = prior index_name and column_position -1 = prior column_position)where index_name is not null) where nums=1)
union
select distinct 'alter table ' || t.table_name || ' move ' || ' LOB(' ||
                T.COLUMN_NAME || ') store as (tablespace CC_MAIN_IDX);'
  from (select t1.table_name, t1.tablespace_name, t2.column_name column_name
          from user_indexes t1
          left join user_tab_cols t2 on t1.table_name = t2.table_name
                                    and t1.index_type = 'LOB'
                                    and t2.data_type like '%LOB') t
 where t.column_name <> '0'
 union
 select 'create unique index '||index_name||' on '||table_name||'('||researchlist||') tablespace  '||tablespace_name||' nologging;' from (
select table_name,tablespace_name,index_name,translate(ltrim(text,'/'),'*/','*,') researchlist from (
select row_number() over(partition by tablespace_name,index_name,table_name order by column_position desc) nums,text,index_name,tablespace_name,table_name from (
select index_name,table_name,column_name,tablespace_name,column_position, sys_connect_by_path(column_name,'/') text from (
select t1.index_name,
       t1.table_name,
       t1.column_name||' '||decode(t1.descend,'DESC',t1.descend) column_name,
       t.tablespace_name,
       t1.column_position
  from user_indexes t
  left join user_ind_columns t1 on t.index_name = t1.index_name
 where T.UNIQUENESS = 'UNIQUE'
   )
connect by index_name = prior index_name and column_position -1 = prior column_position) where index_name is not null) where nums=1);

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10130206/viewspace-616687/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 使用MV总结
下一篇: ORA-02063 ORA-01008
请登录后发表评论 登录
全部评论

注册时间:2009-03-26

  • 博文量
    169
  • 访问量
    548446