This article shows new columns HIGH_VALUE_CLOB type CLOB, HIGH_VALUE_JSON type JSON in dictionary views DBA|ALL|USER_TAB_PARTITIONS

LONG type is still with us in Oracle but you can’t use it directly in sql statments. I wrote about it here:

Converting LONG to CLOB – scaning LONG

It’s huge pain for dictionary views like USER_TAB_PARTITIONS where HIGH_VALUE is very popular to read in warehouse systems

Lets create simple table

CREATE TABLE test_tbl
(
ID NUMBER
)
PARTITION BY RANGE(ID)
(
PARTITION p1 VALUES LESS THAN (100),
PARTITION p2 VALUES LESS THAN (200),
PARTITION p3 VALUES LESS THAN (300)
)
/

List of partitions from dictionary view user_tab_partitions

SELECT table_name, partition_name, high_value, high_value_clob, high_value_json 
FROM user_tab_partitions;

TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_CLOB HIGH_VALUE_JSON
TEST_TBL   P1             100        100          {"high_value":100}
TEST_TBL   P2             200        200          {"high_value":200}
TEST_TBL   P3             300        300          {"high_value":300}

Describe

desc user_tab_partitions

Name Null? Type 
---------------------- ----- -------------- 
TABLE_NAME                   VARCHAR2(128) 
...
HIGH_VALUE                   LONG 
..
HIGH_VALUE_CLOB              CLOB 
HIGH_VALUE_JSON              JSON 

Impossible to run sql because of LONG type column high_value

SELECT table_name, partition_name, high_value, high_value_clob, high_value_json 
FROM user_tab_partitions
WHERE to_number(high_value) < 250;

ORA-00932: expression is of data type LONG, which is incompatible with expected data type CHAR

Now filtering is simpler in 23C with new dictionary columns

high_value_clob

SELECT table_name, partition_name, high_value, high_value_clob, high_value_json 
FROM user_tab_partitions
WHERE table_name='TEST_TBL' 
AND to_number(high_value_clob) < 250;

TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_CLOB HIGH_VALUE_JSON
TEST_TBL P1 100 100 {"high_value":100}
TEST_TBL P2 200 200 {"high_value":200}

high_value_json

SELECT table_name, partition_name, high_value, high_value_clob, high_value_json 
FROM all_tab_partitions a
WHERE table_owner=user and table_name='TEST_TBL' 
AND JSON_VALUE(a.high_value_json, '$.high_value') < 250;

or

SELECT table_name, partition_name, high_value, high_value_clob, high_value_json 
FROM all_tab_partitions a
WHERE table_owner=user and table_name='TEST_TBL' 
AND a.high_value_json.high_value < 250;

TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_CLOB HIGH_VALUE_JSON
TEST_TBL P1 100 100 {"high_value":100}
TEST_TBL P2 200 200 {"high_value":200}

Have a fun 🙂

  Tomasz