Search code examples
oracleoracle-sqldeveloper

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

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

enter image description here

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.

Solution

  • 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