create table t (
x date
) partition by range (x) (
partition p0 values less than (date'2015-01-01'),
partition p1 values less than (date'2015-06-01'),
partition pmax values less than (maxvalue)
);
with xml as (
select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from user_tab_partitions where table_name = ''T''') as x
from dual
)
select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
from xml x,
table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws;
TABLE_NAME PARTITION HIGH_VALUE
---------- ---------- ------------------------------------------------------------------------------------------
T P0 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
T P1 TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
T PMAX MAXVALUE
2条答案
按热度按时间41ik7eoe1#
" 最 佳 " 是 主观 的 , 但 您 可以 通过 查询 数据 字典 来 检索 最 高 值 :
中 的 每 一 个
x8diyxa72#
一种方法是使用XML