26ai Oracle JSON Partitions

{ Abhilash Kumar Bhattaram : Follow on LinkedIn }

Understanding JSON Tables

I have written a 3 part series about JSON vs CLOB earlier , it would be a good reference to check my 3 part series blogs https://dev.to/abhilash8/series/31828 if you are new JSON datatypes in Oracle

Seemingly simple but complex composition of JSON sizes

For example I have 3 tables with JSON dataytpe

--- The below are partitioned tables
SCHEMA01.AUDIT_JSON_T001 with coulums ( id NUMBER , response JSON )
USER01.JSON_TAB01 with coulums ( id NUMBER , response JSON )

--- The below are non partitioned tables
USER01.JSON_TAB01.JSON_TAB01_MSD with coulums ( id NUMBER , response JSON )

If a DBA thinks he can find the size of a JSON partitioned table by quering dba_segments , he is absolutely wrong.

• Oracle JSON tables are a combination of dba_segments, dba_tab_partitions and dba_lob_partitions.
• JSON columns are internally stored as LOB objects

So even oracle own internal function DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS does not seem to recognize
JSON columns as a dependant object

-- js_dep.sql
SET SQLBLANKLINES ON
SET LINESIZE 220
SET PAGESIZE 100
COLUMN partition_name FORMAT A40
COLUMN allocated_mib FORMAT 999999999990.00

WITH partitions_to_size AS (
    SELECT p.table_owner,
           p.table_name,
           p.partition_name,
           p.partition_position
    FROM dba_tab_partitions p
    WHERE p.table_owner = 'SCHEMA01'
      AND p.table_name = 'AUDIT_JSON_T001'
),
segment_keys AS (
    SELECT p.partition_name AS table_partition_name,
           p.table_owner AS segment_owner,
           p.table_name AS segment_name,
           p.partition_name AS segment_partition_name
    FROM partitions_to_size p
    UNION
    SELECT p.partition_name,
           d.segment_owner,
           d.segment_name,
           d.partition_name
    FROM partitions_to_size p,
         TABLE(DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS(
             p.table_owner,
             p.table_name,
             p.partition_name,
             7
         )) d
)
SELECT p.partition_name,
       ROUND(NVL(SUM(s.bytes), 0) / 1048576, 2) AS allocated_mib
FROM partitions_to_size p
LEFT JOIN segment_keys k
  ON k.table_partition_name = p.partition_name
LEFT JOIN dba_segments s
  ON s.owner = k.segment_owner
 AND s.segment_name = k.segment_name
 AND (s.partition_name = k.segment_partition_name
      OR (s.partition_name IS NULL
          AND k.segment_partition_name IS NULL))
 AND s.segment_type IN ('TABLE PARTITION', 'LOB PARTITION', 'LOBINDEX')
GROUP BY p.partition_name, p.partition_position
ORDER BY p.partition_position;


testdb01> ! vi js_dep.sql

testdb01> @js_dep

PARTITION_NAME                          |   ALLOCATED_MIB
----------------------------------------|----------------
PART_INIT                               |            0.00
SYS_P1234                               |            0.00
SYS_P5678                               |        83972.00 ----> seems top use only dba_segments ( does not use lob segments where JSON columns are included )
SYS_P9012                               |       163650.00 ----> seems top use only dba_segments ( does not use lob segments where JSON columns are included )

The above is a WRONG representation of JSON table sizes.

The above examples it is clear that using DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS only fetches data from dba_segments as I am aware I have a ton of data in my JSON columns

Also DBMS_SPACE.OBJECT_DEPENDENT_SEGMENTS does not look beyond dba_segments

Size of JSON a partitioned JSON Table

Please refer my github for js_part.sql which gives a script for

https://github.com/abhilash-8/ora-tools/blob/master/js_part.sql

It is as simple as the following syntax
@js_part USER01 JSON_TAB01

testdb01> @js_part USER01 JSON_TAB01

TABLE_OWNER         |TABLE_NAME        |PARTITION_NAME      |PARTITION_RANGE                                                                           |  TABLE_PART_SIZE_MB|    LOB_PART_SIZE_MB|       TOTAL_SIZE_MB
--------------------|------------------|--------------------|------------------------------------------------------------------------------------------|--------------------|--------------------|--------------------
USER01              |JSON_TAB01        |PART_INIT           |TO_DATE(' 2026-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       |                   0|                   0|                   0
USER01              |JSON_TAB01        |SYS_P3432           |TO_DATE(' 2026-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       |                   0|                   0|                   0
USER01              |JSON_TAB01        |SYS_P3772           |TO_DATE(' 2026-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       |              20,416|           2,194,235|           2,214,651
USER01              |JSON_TAB01        |SYS_P3739           |TO_DATE(' 2026-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       |              23,618|           4,294,537|           4,318,155
                    |                  |                    |                                                                                          |                    |                    |--------------------
sum                 |                  |                    |                                                                                          |                    |                    |           6,532,806

So essentially this summarizes the below

• TABLE_PART_SIZE_MB fetches all space used from regular segments used in the tables for every partition
• LOB_PART_SIZE_MB fetches all space used from LOB segments specifically JSON columns in the table for every partition
• TOTAL_SIZE_MB is ( TABLE_PART_SIZE_MB + LOB_PART_SIZE_MB )
• You can observe the large size used by the LOB_PART_SIZE_MB when compared to the TABLE_PART_SIZE_MB , the LOB_PART_SIZE_MB is where the JSON data is stored.

Size of JSON a non partitioned JSON Table

Please refer my github for js_nopart.sql which gives a script for

https://github.com/abhilash-8/ora-tools/blob/master/js_nopart.sql

It is as simple as the following syntax
@js_nopart USER01 JSON_TAB01_MSD

testdb01> @js_nopart USER01 JSON_TAB01_MSD

TABLE_OWNER         |TABLE_NAME                              |TABLE_SIZE_MB|LOB_SIZE_MB|       TOTAL_SIZE_MB
--------------------|----------------------------------------|-------------|-----------|--------------------
USER01              |JSON_TAB01_MSD                          |        15111|   66071.81|              81,183
                    |                                        |             |           |--------------------
sum                 |                                        |             |           |              81,183


So essentially this summarizes the below

- TABLE_PART_SIZE_MB fetches all space used from regular segments used in the tables 
- LOB_PART_SIZE_MB fetches all space used from LOB segments specifically JSON columns 
- TOTAL_SIZE_MB is ( TABLE_PART_SIZE_MB + LOB_PART_SIZE_MB )  

Summary

Oracle has done exceedingly well in handling large JSON data in data types , but it is a bit difficult to identify sizes of tables per partition , I hope in future there is a function in the database dictionary which directly gives this info. Until then my scripts in github can assist.

--- For Partitioned JSON Tables
https://github.com/abhilash-8/ora-tools/blob/master/js_part.sql

--- For Non Partitioned JSON Tables
https://github.com/abhilash-8/ora-tools/blob/master/js_nopart.sql

How Nabhaas helps you

If you’ve made it this far, you already sense there’s a better way — in fact, you have a way ahead.

If you’d like Nabhaas to assist in your journey, remember — TAB is just one piece. Our Managed Delivery Service ensures your Oracle operations run smoothly between patch cycles, maintaining predictability and control across your environments.

NABTAB - Whitepaper ,
Reference & White Paper

Managed Delivery Services - Whitepaper ,
Reference & White Paper