分区语句整理

分区操作语句

--删除分区

    alter table tb_kr_voice_summary_01_day add partition DATA_20110707 values ('20110707');

 

 

--查看一个分区的数据大小

   SELECT   owner,

         DECODE (partition_name,

                 NULL, segment_name,

                 segment_name || ':' || partition_name

                ) NAME,

         segment_type, tablespace_name, BYTES/1024/1024 , initial_extent, next_extent,

         pct_increase, extents, max_extents

    FROM dba_segments t

   WHERE 1 = 1 AND extents > 1

     and t.segment_name like 'TB_MK%'

ORDER BY 9 DESC,

         3;

 

--统计分区数

select count(*),owner from dba_tables group by owner;

 

-- 查找哪些分区表未配置在"分区维护表"中

select distinct a.table_owner, a.table_name

  from dba_tab_partitions a

 where table_owner in ('LBIDW', 'LBIMK', 'LBIKR')

   and not exists (select 'a'

          from lbidw.tb_sys_tab_partitions b

         where a.table_name = upper(b.table_name));