I need a procedure that will help me to drop partition older than 3 months (current month last 3 older months). And then I need to create the new partition in sequence.
for example if current partition is May22, then I need to drop Jan22 and create Jan23 partition. Can someone help me with the procedure?
CodePudding user response：
Here is the process we use to RENAME and drop PARTITIONs that are range INTERVAL. Just add the procedures to your scheduler.
They work great
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-02-01')
select dt from dt;
/
create index t1_global_ix on t1 (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;
/
CREATE OR REPLACE PROCEDURE rebuild_index
authid current_user
is
BEGIN
for i in (
select index_owner, index_name, partition_name, 'partition' ddl_type
from all_ind_partitions
where status = 'UNUSABLE'
union all
select owner, index_name, null, null
from all_indexes
where status = 'UNUSABLE'
)
loop
if i.ddl_type is null then
ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild parallel 4 online');
else
ddl('alter index '||i.index_owner||'.'||i.index_name||' modify '||i.ddl_type||' '||i.partition_name||' rebuild parallel 4 online');
end if;
end loop;
END;
/
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);
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;
rebuild_index();
END;
CodePudding user response：
Use interval partitioning - available starting from Oracle 11.
You will need no procedure for a creation of a partition, because the partition will be created automatically with the first insert of the data with the corresponding date.
You will still need a basic code for drop partition - rolling window is not supported in the interval partitioning.
Using the partition extended names makes it much easier that if you use partition names
Example
The date for which a partition has to be dropped in the variable v_drop_date, say 22 Jan 2022
v_sql := q'[alter table XXX.TTTTTTT truncate partition for (DATE']'||
to_char(v_drop_date,'YYYY-MM-DD')||q'[') ]';
execute immediate v_sql;
The code generates and executes following statement, that drops the relevant partition
alter table XXX.TTTTTTT truncate partition for (DATE'2022-01-22')
If you use global indexes you may want to add the clause UPDATE INDEXES to keep the index usable after the operation.