8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Enhanced Partitioning Metadata in Oracle Database 23c
Oracle database 23c makes it easier to display the high value of partitions using two new columns in the ALL_TAB_PARTITIONS view.
Related articles.
The Problem
We create a range partitioned table with four partitions.
drop table if exists part_tab purge;
create table part_tab (
created_date date not null,
some_data varchar2(100) not null
)
partition by range (created_date) (
partition part_2021 values less than (to_date('2022-01-01','YYYY-MM-DD')),
partition part_2022 values less than (to_date('2023-01-01','YYYY-MM-DD')),
partition part_2023 values less than (to_date('2024-01-01','YYYY-MM-DD')),
partition part_2024 values less than (to_date('2025-01-01','YYYY-MM-DD'))
);
After creation, if we want to check the partition boundaries we have to query the HIGH_VALUE column value from the USER_TAB_PARTITIONS view.
set linesize 140 long 100 longchunksize 100
column partition_name format a20
column high_value format a85
select partition_name,
high_value
from user_tab_partitions
where table_name = 'PART_TAB'
order by 1;
PARTITION_NAME HIGH_VALUE
-------------------- -------------------------------------------------------------------------------------
PART_2021 TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2022 TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2023 TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2024 TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
4 rows selected.
SQL>
That works fine in SQL*Plus, which understands how to handle a LONG datatype, but working with LONG datatypes can be problematic, especially since they've been deprecated since Oracle8i Release 8.1.6.
The Solution
Oracle database 23c has added the HIGH_VALUE_CLOB and HIGH_VALUE_JSON columns to the {PDB|DBA|ALL|USER}_TAB_PARTITIONS views. This simplifies access to the high value data, and means it is more easily used programmatically.
First, we query the HIGH_VALUE_CLOB column, which is a CLOB datatype.
column high_value_clob format a85
select partition_name,
high_value_clob
from user_tab_partitions
where table_name = 'PART_TAB'
order by 1;
PARTITION_NAME HIGH_VALUE_CLOB
-------------------- -------------------------------------------------------------------------------------
PART_2021 TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2022 TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2023 TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
PART_2024 TO_DATE(' 2025-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
4 rows selected.
SQL>
Next we query the HIGH_VALUE_JSON column, which is a JSON datatype.
column high_value_json format a40
select partition_name,
high_value_json
from user_tab_partitions
where table_name = 'PART_TAB'
order by 1;
PARTITION_NAME HIGH_VALUE_JSON
-------------------- ----------------------------------------
PART_2021 {"high_value":"2022-01-01T00:00:00"}
PART_2022 {"high_value":"2023-01-01T00:00:00"}
PART_2023 {"high_value":"2024-01-01T00:00:00"}
PART_2024 {"high_value":"2025-01-01T00:00:00"}
4 rows selected.
SQL>
SQL*Plus knows how to handle a JSON datatype, but for other tools you may need to serialize the JSON data, or extract the value as a valid datatype. We know our boundary is a date, so we can extract the value and return it as a DATE datatype.
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
column high_value_json format a40
column high_value_date format a20
select partition_name,
json_serialize(high_value_json) as high_value_json,
json_value(high_value_json, '$.high_value' returning date) as high_value_date
from user_tab_partitions
where table_name = 'PART_TAB'
order by 1;
PARTITION_NAME HIGH_VALUE_JSON HIGH_VALUE_DATE
-------------------- ---------------------------------------- --------------------
PART_2021 {"high_value":"2022-01-01T00:00:00"} 01-JAN-2022 00:00:00
PART_2022 {"high_value":"2023-01-01T00:00:00"} 01-JAN-2023 00:00:00
PART_2023 {"high_value":"2024-01-01T00:00:00"} 01-JAN-2024 00:00:00
PART_2024 {"high_value":"2025-01-01T00:00:00"} 01-JAN-2025 00:00:00
4 rows selected.
SQL>
For more information see:
Hope this helps. Regards Tim...