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

最新推荐文章于 2023-02-22 08:58:40 发布

cuiliao2030

最新推荐文章于 2023-02-22 08:58:40 发布

阅读量143

点赞数

文章标签：数据库

今天数据库需要将几个用户里的表索引以某一个用户为标准进行一次统一版本，于是整理了一个脚本方便以后使用，其中涉及到当前表里的索引创建语句、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);