Best way to get high value of partition from oracle database?

CodePudding user response：

"Best" is subjective but you can retrieve the high values by quering the data dictionary:

SELECT partition_name,
       high_value
FROM   USER_TAB_PARTITIONS
WHERE  table_name = 'YOUR_TABLE_NAME';

CodePudding user response：

One way is to use XML

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