分区表工具脚本

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

 

查询未建分区的大表脚本

prompt <p>当前用户下，表大小超过10个GB未建分区的
select segment_name,
       segment_type,
       sum(bytes) / 1024 / 1024 / 1024 object_size
  from user_segments
WHERE segment_type = 'TABLE'
group by  segment_name, segment_type
having sum(bytes) / 1024 / 1024 / 1024 >= 10
order by object_size desc;

查询失效的分区表索引

prompt <p>查询当前用户下，失效-普通索引 
select t.index_name,
       t.table_name,
       blevel,
       t.num_rows,
       t.leaf_blocks,
       t.distinct_keys
  from user_indexes t
where status = 'INVALID';

prompt <p>查询当前用户下的失效-分区索引
select t1.blevel,
       t1.leaf_blocks,
       t1.INDEX_NAME,
       t2.table_name,
       t1.PARTITION_NAME,
       t1.STATUS
  from user_ind_partitions t1, user_indexes t2
where t1.index_name = t2.index_name
   and t1.STATUS = 'UNUSABLE';

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

--以下脚本可以分析分区表记录不平衡的情况，同时也可以从侧面发现由于疏于管理，大量当前数据进入默认分区的情况
/*

   注：这个语句不一定准确，尤其是在表未及时收集统计信息的时候
   
*/

--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;  


--当前用户下，某个分区的记录数是平均记录数的2倍以上
set linesize 266
col table_name format a20
select table_name,
       max(num_rows),
       trunc(avg(num_rows),0),
       sum(num_rows),
       case when sum(num_rows),0 then 0,else trunc(max(num_rows) / sum(num_rows),2) end,
       count(*)
  from user_tab_partitions
 group by table_name
having max(num_rows) / sum(num_rows) > 2 / count(*);

--也可用来作为判断查询当前用户下有因为疏于分区管理导致无大量数据进了建默认分区的参考。

select table_name,
       partition_name,
       num_rows  
 from user_tab_partitions
 where table_name = 'RANGE_PART_TAB'
 order by num_rows desc;


--请试验<分区类型_范围分.sql>后，体会上述脚本

查询分区数过多的表

prompt <p>当前用户下分区最多的前10个对象
select *
  from (select  table_name, count(*) cnt
          from user_tab_partitions
         group by  table_name
         order by cnt desc)
where rownum <= 10;

prompt <p>当前用户下分区个数超过100个的表
select  table_name, count(*) cnt
  from user_tab_partitions
 having count(*) >= 100
group by table_name, table_name
order by cnt desc;

--或者如下更方便
select table_name, partitioning_type, subpartitioning_type
  from user_part_tables
 where partition_count > 100;

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

select table_name, 
       partitioning_type, 
       subpartitioning_type, 
       partition_count
  from user_part_tables
 where subpartitioning_type <> 'NONE';
 
 
select count(*) from user_part_tables where  subpartitioning_type <> 'NONE';

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

select table_name, 
       column_name, 
       data_type
  from user_tab_columns
 where data_type in ( 'LONG','CHAR');











------------------------------------------------------------------------------------

drop table t_long purge;
create table t_long (id int, name long);
drop table t_char purge;
create table t_char (id int, address char(10));
set linesize 266
col table_name  format a25
col column_name format a25
col data_type   format a20

SQL> select table_name,
  2         column_name,
  3         data_type
  4    from user_tab_columns
  5   where data_type in ( 'LONG','CHAR');

TABLE_NAME                COLUMN_NAME             DATA_TYPE
------------------------- ----------------------------------
T_CHAR                    ADDRESS                   CHAR
T_LONG                    NAME                      LONG

查询哪些外键未建索引

--查看当前数据库哪些对象外键没建索引
select table_name,
       constraint_name,
       cname1 || nvl2(cname2, ',' || cname2, null) ||
       nvl2(cname3, ',' || cname3, null) ||
       nvl2(cname4, ',' || cname4, null) ||
       nvl2(cname5, ',' || cname5, null) ||
       nvl2(cname6, ',' || cname6, null) ||
       nvl2(cname7, ',' || cname7, null) ||
       nvl2(cname8, ',' || cname8, null) columns
  from (select b.table_name,
               b.constraint_name,
               max(decode(position, 1, column_name, null)) cname1,
               max(decode(position, 2, column_name, null)) cname2,
               max(decode(position, 3, column_name, null)) cname3,
               max(decode(position, 4, column_name, null)) cname4,
               max(decode(position, 5, column_name, null)) cname5,
               max(decode(position, 6, column_name, null)) cname6,
               max(decode(position, 7, column_name, null)) cname7,
               max(decode(position, 8, column_name, null)) cname8,
               count(*) col_cnt
          from (select substr(table_name, 1, 30) table_name,
                       substr(constraint_name, 1, 30) constraint_name,
                       substr(column_name, 1, 30) column_name,
                       position
                  from user_cons_columns) a,
               user_constraints b
         where a.constraint_name = b.constraint_name
           and b.constraint_type = 'R'
         group by b.table_name, b.constraint_name) cons
 where col_cnt > ALL
 (select count(*)
          from user_ind_columns i
         where i.table_name = cons.table_name
           and i.column_name in (cname1, cname2, cname3, cname4, cname5,
                cname6, cname7, cname8)
           and i.column_position <= cons.col_cnt
         group by i.index_name)


查询所有含外键的表

select count(*),TABLE_NAME,c_constraint_name from (
select a.table_name,       
       substr(a.constraint_name, 1, 30) c_constraint_name,
       substr(a.column_name, 1, 30) column_name,
       position,
       b.owner,
       b.constraint_name,
       b.constraint_type
  from user_cons_columns a, user_constraints b
 where a.constraint_name = b.constraint_name
   and b.constraint_type = 'R' )
   group by TABLE_NAME,c_constraint_name