如何查询user_tab_partitions  视图的 HIGH_VALUE字段？

有时候我们需要查询分区表确定分区的大小。就需要查询user_tab_partitions 视图的 HIGH_VALUE字段。

但是会发现这个字段的类型 是LONG 类型。例如：

SQL> desc user_tab_partitions;
Name                   Type         Nullable Default Comments
---------------------- ------------ -------- ------- --------
TABLE_NAME             VARCHAR2(30) Y                         
COMPOSITE              VARCHAR2(3)  Y                         
PARTITION_NAME         VARCHAR2(30) Y                         
SUBPARTITION_COUNT     NUMBER       Y                         
HIGH_VALUE             LONG         Y                         
HIGH_VALUE_LENGTH      NUMBER       Y                         
PARTITION_POSITION     NUMBER       Y                         
TABLESPACE_NAME        VARCHAR2(30) Y                        

sqlplus 里面显示是TO_DATE 具体的内容：

SQL> select t.table_name, t.partition_name, t.high_value
      from user_tab_partitions t
     where t.table_name = 'PART_IND_TEST'
       and t.partition_name = 'P_201401';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------
PART_IND_TEST                  P_201401                       TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

查询得到的内容是   TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

但是更多的时候需求是希望得到2014-02-01 00:00:00 这个时间。
并且在PL/SQL Developer 查询出来不是显示具体的内容：

创建一个函数，可以巧妙的绕过这个问题：

create or replace function fun_high_value(p_tname   varchar2,
                                          p_parname varchar2)
  return varchar2 is
  l_high_value varchar2(1000);
begin
  select t.high_value
    into l_high_value
    from user_tab_partitions t
   where t.table_name = p_tname
     and t.partition_name = p_parname;
  return l_high_value;
end;

select table_name,
       partition_name,
       fun_high_value(table_name, partition_name) high_value,
       substr(fun_high_value(table_name, partition_name), 10, 11)
  from user_tab_partitions t
 where t.table_name = 'PART_IND_TEST'
   and t.partition_name = 'P_201401';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE                                                                                 SUBSTR(FUN_HIGH_VALUE(TABLE_NAME,PARTITION_N
------------------------------ ------------------------------ ------------------------------------------------------------------------------------------ --------------------------------------------
PART_IND_TEST                  P_201401                       TO_DATE(' 2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')         2014-02-01

另外一个办法是使用XML db的语法查询。也能实现同样的需求：

select *
  from (with xml as (SELECT dbms_xmlgen.getXMLType('select table_name, partition_name, high_value
                                 from user_tab_partitions t
                                where t.table_name = ''' ||
                                                   t.table_name || ''' ') as x
                       from user_tables t
                      where t.TABLE_NAME in ('PART_IND_TEST'))
         select extractvalue(rws.object_value, '/ROW/TABLE_NAME') TABLE_NAME,
                extractvalue(rws.object_value, '/ROW/PARTITION_NAME') PARTITION_NAME,
                extractvalue(rws.object_value, '/ROW/HIGH_VALUE') HIGH_VALUE
           FROM XML X, TABLE(XMLSEQUENCE(EXTRACT(X.X, '/ROWSET/ROW'))) rws)
          where partition_name = 'P_201401'

如果不想创建函数，数据库版本是12c以上的话，可以使用with语法：
with function fun_high_value(p_tname varchar2,
p_parname varchar2) return varchar2 is l_high_value varchar2(1000);
begin
  select t.high_value
    into l_high_value
    from user_tab_partitions t
   where t.table_name = p_tname
     and t.partition_name = p_parname;
  return l_high_value;
end;
select table_name,
       partition_name,
       fun_high_value(table_name, partition_name) high_val
  from user_tab_partitions t
 where t.table_name = 'PART_IND_TEST'
   and t.partition_name = 'P_201401'

以上是几种办法。你学会了么？