Oracle查询分区表的最后一个分区值

Oracle查询分区表的最后一个分区值

这是一个来自生产上的需求：数据库中的分区表（几十上百个）都是根据年份来分区的并且分区名称和分区年份一一对应（比如p2011对应'2011'），客户要求检查分区表的分区是否已经过时。比如现在是2014年，若一个表的最大分区时间为2013年或2012年，则该表的分区已过时，需要考虑增加分区之类的对策。

为了简化问题，这里只创建三个表来分析。
第一个分区表：
create table p1
(
 id        VARCHAR2(10),
 name      VARCHAR2(20)
)
partition by range (id)
(
 partition p2011 values less than ('2011'),
 partition p2012 values less than ('2012'),
 partition p2013 values less than ('2013'),
 partition p2014 values less than ('2014')
);

Table created.

第二个分区表：
create table p2
(
 id        VARCHAR2(10),
 name      VARCHAR2(20)
)
partition by range (id)
(
 partition p2010 values less than ('2010'),
 partition p2011 values less than ('2011'),
 partition p2012 values less than ('2012'),
 partition p2013 values less than ('2013'),
 partition p2014 values less than ('2014')
);

Table created.

第三个分区表：
create table p3
(
 id        VARCHAR2(10),
 name      VARCHAR2(20)
)
partition by range (id)
(
 partition p2010 values less than ('2010'),
 partition p2011 values less than ('2011'),
 partition p2012 values less than ('2012')
);

Table created.

思路：不管每个表有多少个时间分区，最大的时间分区只能有一个，即最后那个。最后的那个时间值是最大的，可以考虑用函数max()。

select table_name, max(partition_name) as max_partition_name from user_tab_partitions group by table_name;

TABLE_NAME    MAX_PARTITION_NAME
--------------- --------------------
P1        P2014
P2        P2014
P3        P2012

如果想把high_value也显示出来，则：
select t1.table_name, t1.max_partition_name, t2.high_value from
(select table_name, max(partition_name) as max_partition_name from user_tab_partitions group by table_name) t1,
(select table_name, partition_name, high_value from user_tab_partitions) t2
where t1.table_name = t2.table_name and t1.max_partition_name = t2.partition_name;

TABLE_NAME    MAX_PARTITION_NAME   HIGH_VALUE
--------------- -------------------- ---------------
P1        P2014             '2014'
P2        P2014             '2014'
P3        P2012             '2012'

从上述结果可以看出P3的数据有问题，分区只分到2012年。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29485627/viewspace-1250172/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29485627/viewspace-1250172/