SQL提取當前庫內索引的建立語句

今天資料庫需要將幾個使用者裡的表索引以某一個使用者為標準進行一次統一版本，於是整理了一個指令碼方便以後使用，其中涉及到當前表裡的索引建立語句、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);