查询oracle分区表信息high_value在PL/SQL Developer查询窗口显示为blob字段,需要打开明细才可以看到分区具体信息,比较繁琐;使用to_char报错误ora-00932,

google一把搜到working with long columns共有四种解决办法:

There are several workarounds we can use to solve our sample problem. We will examine each of the following in turn:

• TO_LOB;
• PL/SQL;
• DBMS_XMLGEN;
• Dictionary Long Application (oracle-developer.net utility)

下面是根据DBMS_XMLGEN可以得到分区表high_value相关信息脚本:

with xs as

   (select x.*,
       to_date (substr (high_value, 11, 19), 'YYYY-MM-DD HH24:MI:SS') dates,
       replace (high_value, 'TIMESTAMP''') datestr
  from xmltable('/ROWSET/ROW' passing (select dbms_xmlgen.getxmltype('
select t.table_owner, t.table_name, t.partition_name, t.high_value
  from dba_tab_partitions t')
                      from dual) columns
                   table_owner varchar2(30) path 'TABLE_OWNER',
                   table_name varchar2(30) path 'TABLE_NAME',
                   partition_name varchar2(30) path 'PARTITION_NAME',
                   high_value varchar2(30) path 'HIGH_VALUE') x )
select xs.datestr,p.* from xs,dba_tab_partitions p
where xs.table_owner = 'BANK'
 and p.table_owner = xs.table_owner
 and p.table_name = xs.table_name
 and p.partition_name = xs.partition_name
 order by p.table_owner,p.table_name,p.partition_position;