I'm trying to write some PL SQL that will drop PARTITIONs from a table based on a date.
I want to avoid dropping the greatest PARTITION to avoid throwing the error:
ORA-14083: cannot drop the only partition of a partitioned table
But when I try creating the procedure drop_partition I'm getting the following error.
Errors: PROCEDURE DROP_PARTITION
Line/Col: 18/5 PL/SQL: Statement ignored
Line/Col: 19/40 PLS-00204: function or pseudo-column 'MAX' may be used inside a SQL statement only
Is there a workarund to help me get past this issue? Any help and expertise would be greatly appreciated. Thanks to all who answer. Below is my test CASE.
CREATE OR REPLACE PROCEDURE ddl(p_cmd varchar2)
authid current_user
is
BEGIN
dbms_output.put_line(p_cmd);
execute immediate p_cmd;
END;
/
CREATE OR REPLACE PROCEDURE
drop_partition(
p_tab varchar2,
p_date date
) authid current_user
is
v_high_value date;
cursor v_cur is
select table_name,
partition_name,
high_value,
partition_position
from user_tab_partitions
where table_name = upper(p_tab);
begin
for v_rec in v_cur loop
execute immediate 'select ' || v_rec.high_value || ' from dual' into v_high_value;
if v_high_value <= trunc(p_date)
and v_rec.partition_position != MAX(v_rec.partition_position)
then
ddl('alter table '||p_tab||' drop partition '||v_rec.partition_name);
end if;
end loop;
END;
/
CREATE TABLE partition_retention
(
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
TABLE_NAME VARCHAR2(30),
DAYS NUMBER(6),
CONSTRAINT
partition_retention_pk primary key (table_name));
/
INSERT into partition_retention(TABLE_NAME, DAYS)
WITH data as (
select 'T1', 0
from dual union all
select 'T3', 15
from dual union all
select 'T4', 10
from dual union all
select 'T5', 5
from dual)
SELECT * from data;
/
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 into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt + interv, interv from dt
where dt.dt + interv < date '2022-02-01')
select dt from dt;
/
BEGIN
FOR td IN
(
SELECT table_name
, NVL (pr.days, 30) AS days
FROM user_part_tables pt
JOIN user_part_key_columns pkc ON pkc.name = pt.table_name
JOIN user_tab_cols tc USING (table_name, column_name)
LEFT JOIN partition_retention pr USING (table_name)
WHERE pkc.object_type = 'TABLE'
AND pt.partitioning_type = 'RANGE'
AND REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*')
ORDER BY table_name
)
LOOP
drop_partition(
td.table_name,
trunc(sysdate-td.days)
);
END LOOP;
END;
/