I have a setup below that is renaming partitions and dropping those partitions based on retention and PARTITION name. Everything appears to be working properly.
I am trying to update the code to use PARTITION extended names (high value DATE) to drop and or truncate the PARTITION if needed.
There is a call below, which is commented out that throws an error on the to_char() part of the statement, which I don't understand why and don't know how to fix.
To produce the error just uncomment the line and comment the line above.
Apologies for the verbose test CASE but I wanted to supply as much information as possible.
Any help would be greatly appreciated. Thanks in advance to all who answer and for your time and expertise.
CREATE OR REPLACE PROCEDURE ddl(p_cmd varchar2)
authid current_user
is
t1 pls_integer;
BEGIN
t1 := dbms_utility.get_time;
dbms_output.put_line(p_cmd);
execute immediate p_cmd;
dbms_output.put_line((dbms_utility.get_time - t1)/100 || ' seconds');
END;
/
CREATE TABLE PARTITION_RETENTION (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
TABLE_NAME VARCHAR2(30),
RETENTION INTERVAL DAY(3) TO SECOND(0),
CONSTRAINT
partition_retention_pk primary key (table_name),
CONSTRAINT CHK_NON_ZERO_DAYS CHECK (
RETENTION > INTERVAL '0' DAY
),
CONSTRAINT CHK_WHOLE_DAYS CHECK (
EXTRACT(HOUR FROM RETENTION) = 0
AND EXTRACT(MINUTE FROM RETENTION) = 0
AND EXTRACT(SECOND FROM RETENTION) = 0
)
);
insert into PARTITION_RETENTION (TABLE_NAME, RETENTION)
select 'T1', interval '10' day from dual union all
select 'T3', interval '15' day from dual union all
select 'T4', 15 * interval '1' day from dual union all
select 'T5', 5 * interval '1 00:00:00' day to second from dual;
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-03-11')
select dt from dt;
/
CREATE OR REPLACE PROCEDURE MaintainPartitions IS EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);
CURSOR PartTables IS
SELECT TABLE\_NAME, INTERVAL
FROM USER\_PART\_TABLES
WHERE PARTITIONING\_TYPE = 'RANGE'
ORDER BY TABLE\_NAME;
CURSOR TabParts(aTableName VARCHAR2) IS
SELECT PARTITION\_NAME, HIGH\_VALUE
FROM USER\_TAB\_PARTITIONS
WHERE regexp_like(partition_name,'^SYS_P[[:digit:]]{1,10}') AND
TABLE_NAME = aTableName AND
table_name not like 'BIN$%'
and interval is not null
ORDER BY PARTITION_POSITION;
ym INTERVAL YEAR TO MONTH;
ds INTERVAL DAY TO SECOND;
newPartName VARCHAR2(30);
PERIOD TIMESTAMP;
BEGIN
FOR aTab IN PartTables LOOP
BEGIN
EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ds;
ym := NULL;
EXCEPTION
WHEN EXPRESSION\_IS\_OF\_WRONG\_TYPE THEN
EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.INTERVAL||'; END;' USING OUT ym;
ds := NULL;
END;
FOR aPart IN TabParts(aTab.TABLE\_NAME) LOOP
EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH\_VALUE||'; END;' USING OUT PERIOD;
IF ds IS NOT NULL THEN
IF ds >= INTERVAL '7' DAY THEN
-- Weekly partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO\_CHAR('||aPart.HIGH\_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P\_"IYYY"W"IW';
ELSE
-- Daily partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO\_CHAR('||aPart.HIGH\_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P\_"YYYYMMDD';
END IF;
ELSE
IF ym = INTERVAL '3' MONTH THEN
-- Quarterly partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO\_CHAR('||aPart.HIGH\_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P\_"YYYY"Q"Q';
ELSE
-- Monthly partition
EXECUTE IMMEDIATE 'BEGIN :ret := TO\_CHAR('||aPart.HIGH\_VALUE||' - :int, :fmt); END;' USING OUT newPartName, INTERVAL '1' DAY, '"P\_"YYYYMM';
END IF;
END IF;
IF newPartName \<> aPart.PARTITION\_NAME THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||aTab.TABLE\_NAME||' RENAME PARTITION '||aPart.PARTITION\_NAME||' TO '||newPartName;
END IF;
END LOOP;
END LOOP;
END MaintainPartitions;
/
EXEC MaintainPartitions;
DECLARE
CANNOT_DROP_LAST_PARTITION EXCEPTION;
PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);
CANNOT\_DROP\_ONLY\_ONE\_PARTITION EXCEPTION;
PRAGMA EXCEPTION\_INIT(CANNOT\_DROP\_ONLY\_ONE\_PARTITION, -14083);
ts TIMESTAMP;
CURSOR TablePartitions IS
SELECT TABLE_NAME, PARTITION_NAME, p.HIGH_VALUE, t.INTERVAL, RETENTION, DATA_TYPE
FROM USER_PART_TABLES t
JOIN USER_TAB_PARTITIONS p USING (TABLE_NAME)
JOIN USER_PART_KEY_COLUMNS pk ON pk.NAME = TABLE_NAME
JOIN USER_TAB_COLS tc USING (TABLE_NAME, COLUMN_NAME)
JOIN PARTITION_RETENTION r USING (TABLE_NAME)
WHERE pk.object_type = 'TABLE' AND
t.partitioning_type = 'RANGE' AND
REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*');
BEGIN
FOR aPart IN TablePartitions LOOP
EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH\_VALUE||'; END;' USING OUT ts;
IF ts \< SYSTIMESTAMP - aPart.RETENTION THEN
BEGIN
ddl('alter table '||aPart.TABLE\_NAME||' drop partition '||aPart.partition\_name);
/*
PROBLEM HERE
ddl( 'ALTER TABLE ' || aPart.TABLE_NAME || ' DROP PARTITION FOR(DATE ' || TO_CHAR(aPart.high_value,'YYYY-MM-DD') || ')');
*/
EXCEPTION
WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN
DBMS_OUTPUT.PUT_LINE('Cant drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
ddl('ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME);
WHEN CANNOT_DROP_LAST_PARTITION THEN
BEGIN
DBMS_OUTPUT.PUT_LINE('Drop last partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE\_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';
EXCEPTION
WHEN CANNOT\_DROP\_ONLY\_ONE\_PARTITION THEN
-- Depending on the order the "last" partition can be also the "only" partition at the same time
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE\_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';
DBMS_OUTPUT.PUT_LINE('Cant drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
ddl('ALTER TABLE '||aPart.TABLE\_NAME||' TRUNCATE PARTITION '||aPart.PARTITION\_NAME);
END;
END;
END IF;
END LOOP;
END;