业务经常使用date，timestamp 列做RANGE分区的信息，但是 dba_tab_partitions 视图 HIGH_VALUE 列为 LONG类型，操作起来各种限制，网上深入搜索后发现使用 dbms_xmlgen.getXMLType 很方便，特记录一下，方便大家快速处理

with xml as (
   select dbms_xmlgen.getXMLType('select table_owner,table_name, partition_name, high_value
                                    from dba_tab_partitions t 
                                   where 1=1
                                     and t.table_owner = ''' || t.owner || ''' 
                                     and t.table_name = ''' || t.table_name || ''' ') as x
    from dba_part_tables t  
   where 1=1
     and t.partitioning_type = 'RANGE'
     --and t.interval is not null
     and t.owner=upper('SYS') 
     and t.table_name =upper('WRH$_FILESTATXS')
)
select extractvalue(rws.object_value, '/ROW/TABLE_OWNER') table_owner,
       extractvalue(rws.object_value, '/ROW/TABLE_NAME') TABLE_NAME,
       extractvalue(rws.object_value, '/ROW/PARTITION_NAME') PARTITION_NAME,
       substr(regexp_replace(replace(extractvalue(rws.object_value, '/ROW/HIGH_VALUE'),'HH24',''), '[^0-9]', ''),0,8) HIGH_VALUE,
       extractvalue(rws.object_value, '/ROW/HIGH_VALUE') HIGH_VALUE_DESC
  from XML X, 
       TABLE(XMLSEQUENCE(EXTRACT(X.X, '/ROWSET/ROW'))) rws

查询结果如下

再次优化后的sql如下，一键获取

with tmp_part_table as (
 select dtp.table_owner,
        dtp.table_name,
        dpt.partitioning_type,
        dpt.interval as partitioning_interval,
        (select LISTAGG(dpkc.column_name, ',')  WITHIN GROUP (ORDER BY dpkc.column_name) 
           from dba_part_key_columns dpkc 
          where 1=1
            and dpkc.owner=dtp.table_owner 
            and dpkc.name=dtp.table_name
            and dpkc.object_type='TABLE' ) as partition_column,
        (select LISTAGG(dtc.DATA_TYPE, ',')  WITHIN GROUP (ORDER BY dpkc.column_name) 
           from dba_part_key_columns dpkc,
                dba_tab_columns  dtc 
          where 1=1
            and dpkc.owner=dtp.table_owner 
            and dpkc.name=dtp.table_name
            and dpkc.object_type='TABLE' 
            and dpkc.owner=dtc.OWNER 
            and dpkc.name=dtc.TABLE_NAME 
            and dpkc.column_name=dtc.COLUMN_NAME ) as partition_column_datatype,
        dtp.partition_name,
        dtp.partition_position,
        extractvalue(dbms_xmlgen.getXMLType('select table_owner,table_name, partition_name, high_value from dba_tab_partitions t where t.table_owner = ''' || dtp.table_owner || ''' and t.table_name = ''' || dtp.table_name || ''' and t.partition_name = ''' || dtp.partition_name || '''  '), '/ROWSET/ROW/HIGH_VALUE') as high_value_str,
        rank() over (partition by dtp.table_owner,dtp.table_name order by dtp.partition_position desc ) as partition_position_rank_desc
   from dba_part_tables dpt,
        dba_tab_partitions dtp
  where 1=1
    and dpt.owner not in ('OGG','SYS','SYSTEM','OUTLN','AUDSYS','DBSNMP','APPQOSSYS','GSMADMIN_INTERNAL','DBSFWUSER','ORDDATA','MDSYS','CTXSYS','OLAPSYS','FLOWS_FILES','OWBSYS','EXFSYS','APEX_030200','SCOTT','ORDSYS','SYSMAN','XDB','WMSYS')
    and dpt.owner=dtp.table_owner
    and dpt.table_name=dtp.table_name
    and (dpt.owner,dpt.table_name ) not in (
         select dr.owner,dr.object_name
          from dba_recyclebin dr
        )
    --and dpt.partitioning_type not in ('LIST','HASH') 
)
 select *
   from tmp_part_table t0
  where 1=1
    and t0.partition_position_rank_desc =1   
  order by t0.table_owner,t0.table_name
;