CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
user13906258
user13906258

Reputation: 248

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.

Upvotes: 0

Views: 465

Answers (2)

Alex Poole
Alex Poole

Reputation: 191520

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

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59563

You can try this one:

DECLARE
    d DATE;
BEGIN

    FOR aPart IN (SELECT * FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME = 'INSURANCE_PAYMENT') LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT d;
        IF d = TO_DATE('2024-05-29', 'YYYY-MM-DD') THEN
            DBMS_OUTPUT.PUT_LINE(aPart.PARTITION_NAME );
            EXECUTE IMMEDIATE 'ALTER TABLE INSURANCE_PAYMENT TRUNCATE PARTITION ' || aPart.PARTITION_NAME; 
        END IF;
    END LOOP;

END;

Upvotes: 2

Related Questions