Fetch the partition name from ALL_TAB_PARTITIONS table using the partition value in Oracle

Question

I am newbie to Oracle and there is a list partitioned table based on a date column in the database. I am trying to create a table with one static partition and using automatic keyword to create next partitions automatically using the column name. Below is the syntax of my table DDL

CREATE TABLE INSURANCE_PAYMENT 
   (CLAIM_NUMBER VARCHAR2(26 CHAR), 
    CHECK_NUMBER VARCHAR2(20 CHAR), 
    CHECK_DATE TIMESTAMP, 
    PAY_TYPE VARCHAR2(10 CHAR), 
    CHECK_AMOUNT NUMBER(38,2) 
    D_CYCLE_DATE_R DATE
   )
   PARTITION BY LIST (D_CYCLE_DATE_R) AUTOMATIC (
   PARTITION P1 VALUES (TO_DATE('2024-05-29', 'YYYY-MM-DD'))); 

If query the ALL_TAB_PARTITIONS table to check the partition columns of the table it is giving below result

P1 is static partition and SYS_P6533 is a dynamic partition and I am trying to fetch partition name using partition value for example the P1 partition created for date '2024-05-29' using below query

SELECT PARTITION_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = 'INSURANCE_PAYMENT' AND HIGH_VALUE = 
TO_NUMBER(TO_CHAR(TO_DATE('2024-05-29', 'YYYY-MM-DD'), 'YYYYMMDDHH24MISS'))

I am getting below error

ORA-00997: illegal use of LONG datatype
00997. 00000 -  "illegal use of LONG datatype"
*Cause:    
*Action:
Error at Line: 11 Column: 51

I am confused it says HIGH_VALUE is long type, but value inserted in the table for it looks weird "TO_DATE(' 2024-05-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')". I want the Partition column name to truncate the data in the partition as per the business requirements.

Please help me how can i get the partition name based on the partition value or is there any way to get the partition name. Please let me know if you need any more information.

Answer 1

It's possible to get the partition name, but it's not simple; here's one way you could adapt.

I want the Partition column name to truncate the data in the partition

You don't need to know its name, you can use partition for (<values>).

If you start off with say three partitions, one manual and two automatic, with data only in the automatic ones:

select partition_name, high_value from user_tab_partitions

PARTITION_NAME	HIGH_VALUE
P1	TO_DATE(' 2024-05-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
SYS_P23798	TO_DATE(' 2024-06-26 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
SYS_P23799	TO_DATE(' 2024-07-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')

select d_cycle_date_r
from insurance_payment

D_CYCLE_DATE_R
26-JUN-24
24-JUL-24

Then you can query based on the partition value:

select d_cycle_date_r
from insurance_payment partition for (date '2024-06-26')

D_CYCLE_DATE_R
26-JUN-24

select d_cycle_date_r
from insurance_payment partition for (date '2024-07-24')

D_CYCLE_DATE_R
24-JUL-24

and you can truncate a partition in the same way:

alter table insurance_payment
truncate partition for (date '2024-06-26')

select d_cycle_date_r
from insurance_payment

D_CYCLE_DATE_R
24-JUL-24

fiddle

---

You can use the same mechanism to drop partitions as well:

alter table insurance_payment
drop partition for (date '2024-06-26')

alter table insurance_payment
drop partition for (date '2024-05-29')

select d_cycle_date_r
from insurance_payment

D_CYCLE_DATE_R
24-JUL-24

select partition_name, high_value from user_tab_partitions

PARTITION_NAME	HIGH_VALUE
SYS_P23754	TO_DATE(' 2024-07-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')

fiddle