DBA Hub

📋Steps in this guide1/2

Enhanced Partitioning Metadata in Oracle Database 23ai/26ai

Oracle database 23ai/26ai makes it easier to display the high value of partitions using two new columns in the ALL_TAB_PARTITIONS view.

oracle 23configurationintermediate
by OracleDba
14 views
1

The Problem

We create a range partitioned table with four partitions. After creation, if we want to check the partition boundaries we have to query the column value from the view. That works fine in SQL*Plus, which understands how to handle a datatype, but working with datatypes can be problematic, especially since they've been deprecated since Oracle8i Release 8.1.6.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
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'))
);

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>
2

The Solution

Oracle database 23ai/26ai has added the and columns to the views. This simplifies access to the high value data, and means it is more easily used programmatically. First, we query the column, which is a datatype. Next we query the column, which is a datatype. SQL*Plus knows how to handle a 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 datatype. For more information see: - ALL_TAB_PARTITIONS - Partitioning : All Articles Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
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>

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!