Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

How to Drop Interval Partition day wise

Rajan SwMay 14 2024

Hi Experts,

I have table Token and this is interval partitioned day wise and this is on Golden gate where we have some issues using DDL like

ALTER TABLE Token drop partition <Partition_Name>, while checking I came across we have a syntax something like below

**alter table TOKEN drop partition for ( TO_DATE ('2024-05-13', 'YYYY-MM-DD')) ** but my requirment is to drop all the partiion which are 2 days old. Currently I am putting into a cursor and dropping it.

Is there any way where I can directly use the statement like this alter table TOKEN drop partition for < ( TO_DATE ('2024-05-13', 'YYYY-MM-DD')) .

will much appreciate any leads or better ways to handle this please

I am using oracle 19C and the sample table creation scripts.

CREATE SEQUENCE TokenId START WITH 1000000 INCREMENT BY 5 CACHE 100 NOORDER ;

CREATE TABLE TOKEN (
USER_REFRESH_TOKEN_ID NUMBER DEFAULT TOKENID.NEXTVAL NOT NULL,
ORG_ID VARCHAR2(50) NOT NULL,
USER_NAME VARCHAR2(100) NOT NULL,
REFRESH_TOKEN VARCHAR2(32) NOT NULL,
EXPIRY_DATE DATE NOT NULL,
ISSUED_DATE TIMESTAMP(6) NOT NULL,
INITIAL_ISSUED_DATE TIMESTAMP(6) NOT NULL,
APP_ID VARCHAR2(26),
CHANNEL VARCHAR2(26),
USER_INFO VARCHAR2(256) NOT NULL,
IS_USED INTEGER NOT NULL,
CREATED_DATE TIMESTAMP(6),
UPDATED_DATE TIMESTAMP(6),
CREATED_BY VARCHAR2(100),
UPDATED_BY VARCHAR2(100),
CONSTRAINT PK_USER_REFRESH_TOKEN PRIMARY KEY (USER_REFRESH_TOKEN_ID) USING INDEX TABLESPACE ngaflxadm_indx,
CONSTRAINT UQ01_REF_TOKEN UNIQUE (REFRESH_TOKEN) USING INDEX TABLESPACE ngaflxadm_indx
) PARTITION BY RANGE (EXPIRY_DATE)
INTERVAL (NUMTODSINTERVAL (1, 'DAY'))
STORE IN (ngaflxadm_data)
( PARTITION TRANSITION_PARTITION_URT VALUES LESS THAN ( TO_DATE('2000-01-01 00:00:00', 'YYYY:MM:DD HH24:MI:SS'))
) tablespace ngaflxadm_data ENABLE ROW MOVEMENT;

Comments
Post Details
Added on May 14 2024
2 comments
85 views