I have a situation where I'm trying to create an automated process that drops old PARTITIONS based on a retention period.
As you can see below I have a table called partition_retention, which has two entries for tables T1 & T3 and nothing for table T2 even though I have a table with that name.
In the anonymous block below I'm trying to test an exception handler to set the retention to 30 if no entry is found for that table.
When I am testing with livesql I am getting the following error:
ORA-06550: line 21, column 1:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following….
Can someone let me know how to rectify this issue as I prefer to use this method instead of rewriting the code.
Thanks in advance to all who answer and your expertise. Apologies for the somewhat large test CASE as I am trying to be thorough.
ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';
create table partition_retention
(
TABLE_NAME VARCHAR2(30) NOT NULL,
DAYS NUMBER(6),
CONSTRAINT Check_gt0
CHECK (DAYS> 0)
);
/
INSERT into partition_retention
(TABLE_NAME, DAYS)
VALUES
('T1', 15);
/
INSERT into partition_retention
(TABLE_NAME, DAYS)
VALUES
('T3', 5);
/
CREATE TABLE t1 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/
INSERT /*+ APPEND */ into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(30,'MINUTE') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/
create index ix_local on t1 (dt) local;
/
CREATE TABLE t2
(
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/
INSERT /*+ APPEND */ into t2 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(30,'MINUTE') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-01-15')
select dt from dt;
/
create index ix_global on t2 (dt);
/
CREATE TABLE t3 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt TIMESTAMP)
PARTITION BY RANGE (dt)
INTERVAL ( NUMTODSINTERVAL (1, 'DAY') ) (
PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP '2022-01-01 00:00:00.000000')
);
/
INSERT /*+ APPEND */ into t3 (dt)
SELECT TIMESTAMP '2022-01-01 00:00:00'
+ (LEVEL - 1) * INTERVAL '5' MINUTE
+ MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND
FROM DUAL
CONNECT BY
TIMESTAMP '2022-01-01 00:00:00'
+ (LEVEL - 1) * INTERVAL '5' MINUTE
+ MOD(LEVEL - 1, 10) * INTERVAL '0.1' SECOND < DATE '2022-01-15';
/
DECLARE
v_str VARCHAR2 (500);
v_days NUMBER := 0;
BEGIN
FOR cur_r IN(
SELECT TABLE_NAME, PARTITIONING_TYPE, COLUMN_NAME, DATA_TYPE
FROM USER_PART_TABLES
JOIN USER_PART_KEY_COLUMNS ON NAME = TABLE_NAME
JOIN USER_TAB_COLS USING (TABLE_NAME, COLUMN_NAME)
where OBJECT_TYPE = 'TABLE' AND
PARTITIONING_TYPE='RANGE' AND
regexp_like(DATA_TYPE,'^DATE$|^TIMESTAMP*')
)
LOOP
--DBMS_OUTPUT.put_line('Table '|| cur_r.table_name);
select DAYS
into v_days
FROM partition_retention
where TABLE_NAME = cur_r.table_name;
exception
when NO_DATA_FOUND THEN
v_days := 30;
END LOOP;
END;