Oracle Partition table - high_value 읽기

출처 - https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9522731800346411985

 

내용 -

You Asked

Hi, Chris and Connor,

I'm trying to find the best way to get "high value" of partitions from data dictionary, in order to write a customized statistics gathering program.

As you know, the data type of "high_value" column of DBA_TAB_PARITTIONS is LONG, which is difficult to handle by SQL, even the value might be composed by several columns in case of multi-key partition.

Now, I have to use PL/SQL to fetch the LONG value into a VARCHAR variable firstly, but it's hard to handle high values of multiple keys because the separator comma exists here and there within the "high_value" column.
Another way I tried is using the "high_value" column to construct a dynamic SQL then fetch values into various variables, obviously it's expensive.

Is there any good idea? Thanks ahead. 

and we said...

Adrian Billington discusses several options for working with longs at:

http://www.oracle-developer.net/display.php?id=430 

Here's a quick example borrowed from there showing how to get this in SQL using XML conversion:

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