【分区表】分区表最大分区字段信息收集

1. 分区表分区最大分区字段sql生成

SELECT
'select nvl(to_char(max(' || column_name || ')),''0'') from ' || owner || '.' || name || ' union all'
FROM
DBA_PART_KEY_COLUMNS
WHERE
name IN ( SELECT DISTINCT table_name FROM dba_tab_partitions WHERE tablespace_name NOT IN ( 'SYSAUX', 'SYSTEM' ) )
ORDER BY
name;

2. 分区表时间上限查询
   WITH pt1 AS (
   SELECT
   table_owner,
   table_name,
   --partition_name,
   decode(
   instr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 'TIMESTAMP' ),
   1,
   substr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 12, 11 ),
   0,
   decode(
   instr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 'TO_DATE' ),
   1,
   substr( sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name ), 11, 11 ),
   0,
   sys.long_2_varchar ( t2.table_owner, t2.table_name, t2.partition_name )
   )
   ) ptime
   FROM
   all_tab_partitions t2
   WHERE
   TABLE_OWNER NOT IN ( 'SYS', 'SYSTEM' )
   AND table_name IN ( SELECT table_name FROM dba_part_tables WHERE OWNER NOT IN ( 'SYS', 'SYSTEM' ) AND partitioning_type = 'RANGE' )
   ) SELECT
   pt1.table_owner,
   pt1.table_name,
   max( pt1.ptime ) max_partition_time
   FROM
   pt1
   GROUP BY
   pt1.table_owner,
   pt1.table_name
   ORDER BY
   max_partition_time;

***long_2_varchar
CREATE
OR REPLACE FUNCTION sys.long_2_varchar ( p_table_owner IN all_tab_partitions.table_owner % TYPE, p_table_name IN all_tab_partitions.table_name % TYPE, p_partition_name IN all_tab_partitions.partition_name % TYPE )
return VARCHAR2 AS l_high_value LONG;
BEGIN
SELECT
high_value INTO l_high_value
FROM
all_tab_partitions
WHERE
table_owner = p_table_owner
AND table_name = p_table_name
AND partition_name = p_partition_name;
return substr( l_high_value, 1, 4000 );

END;