普通表转化成分区表的高效脚本

 

 

查询未建分区的大表脚本

1 prompt 
当前用户下，表大小超过10个GB未建分区的2 select segment_name,3        segment_type,4        sum(bytes) / 1024 / 1024 / 1024 object_size5   from user_segments6 WHERE segment_type = 'TABLE'7 group by  segment_name, segment_type8 having sum(bytes) / 1024 / 1024 / 1024 >= 109 order by object_size desc;

 

查询失效的分区表索引

1 prompt 
查询当前用户下，失效-普通索引  2 select t.index_name, 3        t.table_name, 4        blevel, 5        t.num_rows, 6        t.leaf_blocks, 7        t.distinct_keys 8   from user_indexes t 9 where status = 'INVALID';10 11 prompt 
查询当前用户下的失效-分区索引12 select t1.blevel,13        t1.leaf_blocks,14        t1.INDEX_NAME,15        t2.table_name,16        t1.PARTITION_NAME,17        t1.STATUS18   from user_ind_partitions t1, user_indexes t219 where t1.index_name = t2.index_name20    and t1.STATUS = 'UNUSABLE';

 

查询分区表各分区大小严重不均匀情况

1 --以下脚本可以分析分区表记录不平衡的情况，同时也可以从侧面发现由于疏于管理，大量当前数据进入默认分区的情况 2 /* 3  4    注：这个语句不一定准确，尤其是在表未及时收集统计信息的时候 5     6 */ 7  8 --统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试 9 exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  10 11 12 --当前用户下，某个分区的记录数是平均记录数的2倍以上13 set linesize 26614 col table_name format a2015 select table_name,16        max(num_rows),17        trunc(avg(num_rows),0),18        sum(num_rows),19        case when sum(num_rows),0 then 0,else trunc(max(num_rows) / sum(num_rows),2) end,20        count(*)21   from user_tab_partitions22  group by table_name23 having max(num_rows) / sum(num_rows) > 2 / count(*);24 25 --也可用来作为判断查询当前用户下有因为疏于分区管理导致无大量数据进了建默认分区的参考。26 27 select table_name,28        partition_name,29        num_rows  30  from user_tab_partitions31  where table_name = 'RANGE_PART_TAB'32  order by num_rows desc;33 34 35 --请试验
      <分区类型_范围分.sql>
       后，体会上述脚本
      

 

查询分区数过多的表

1 prompt 
当前用户下分区最多的前10个对象 2 select * 3   from (select  table_name, count(*) cnt 4           from user_tab_partitions 5          group by  table_name 6          order by cnt desc) 7 where rownum <= 10; 8  9 prompt 
当前用户下分区个数超过100个的表10 select  table_name, count(*) cnt11   from user_tab_partitions12  having count(*) >= 10013 group by table_name, table_name14 order by cnt desc;15 16 --或者如下更方便17 select table_name, partitioning_type, subpartitioning_type18   from user_part_tables19  where partition_count > 100;

 

查询当前有多少带子分区的分区表

1 select table_name, 2        partitioning_type, 3        subpartitioning_type, 4        partition_count5   from user_part_tables6  where subpartitioning_type <> 'NONE';7  8  9 select count(*) from user_part_tables where  subpartitioning_type <> 'NONE';

 

查询表中有没有过时类型的字段

1 select table_name,  2        column_name,  3        data_type 4   from user_tab_columns 5  where data_type in ( 'LONG','CHAR'); 6  7  8  9 10 11 12 13 14 15 16 17 ------------------------------------------------------------------------------------18 19 drop table t_long purge;20 create table t_long (id int, name long);21 drop table t_char purge;22 create table t_char (id int, address char(10));23 set linesize 26624 col table_name  format a2525 col column_name format a2526 col data_type   format a2027 28 SQL> select table_name,29   2         column_name,30   3         data_type31   4    from user_tab_columns32   5   where data_type in ( 'LONG','CHAR');33 34 TABLE_NAME                COLUMN_NAME             DATA_TYPE35 ------------------------- ----------------------------------36 T_CHAR                    ADDRESS                   CHAR37 T_LONG                    NAME                      LONG

 

查询哪些外键未建索引

1 --查看当前数据库哪些对象外键没建索引 2 select table_name, 3        constraint_name, 4        cname1 || nvl2(cname2, ',' || cname2, null) || 5        nvl2(cname3, ',' || cname3, null) || 6        nvl2(cname4, ',' || cname4, null) || 7        nvl2(cname5, ',' || cname5, null) || 8        nvl2(cname6, ',' || cname6, null) || 9        nvl2(cname7, ',' || cname7, null) ||10        nvl2(cname8, ',' || cname8, null) columns11   from (select b.table_name,12                b.constraint_name,13                max(decode(position, 1, column_name, null)) cname1,14                max(decode(position, 2, column_name, null)) cname2,15                max(decode(position, 3, column_name, null)) cname3,16                max(decode(position, 4, column_name, null)) cname4,17                max(decode(position, 5, column_name, null)) cname5,18                max(decode(position, 6, column_name, null)) cname6,19                max(decode(position, 7, column_name, null)) cname7,20                max(decode(position, 8, column_name, null)) cname8,21                count(*) col_cnt22           from (select substr(table_name, 1, 30) table_name,23                        substr(constraint_name, 1, 30) constraint_name,24                        substr(column_name, 1, 30) column_name,25                        position26                   from user_cons_columns) a,27                user_constraints b28          where a.constraint_name = b.constraint_name29            and b.constraint_type = 'R'30          group by b.table_name, b.constraint_name) cons31  where col_cnt > ALL32  (select count(*)33           from user_ind_columns i34          where i.table_name = cons.table_name35            and i.column_name in (cname1, cname2, cname3, cname4, cname5,36                 cname6, cname7, cname8)37            and i.column_position <= cons.col_cnt38          group by i.index_name)39 40 41 查询所有含外键的表42 43 select count(*),TABLE_NAME,c_constraint_name from (44 select a.table_name,       45        substr(a.constraint_name, 1, 30) c_constraint_name,46        substr(a.column_name, 1, 30) column_name,47        position,48        b.owner,49        b.constraint_name,50        b.constraint_type51   from user_cons_columns a, user_constraints b52  where a.constraint_name = b.constraint_name53    and b.constraint_type = 'R' )54    group by TABLE_NAME,c_constraint_name55 56