I have a requirement, we are creating partitions on daily basis on date so, need to drop older partitions which are older than 30 days.
Is there any best approach to drop partition without using alter drop command? Can we set expiry date or time on partition which drop the partitions?
CodePudding user response：
partition_number is a number of partition/day you can keep
table_name table name with partition
CREATE OR REPLACE PROCEDURE REMOVE_OLD_PARTITIONS(table_nameIN VARCHAR2, partition_number NUMBER) IS
stm VARCHAR2(100);
last_partition NUMBER;
BEGIN
BEGIN
SELECT MAX(PARTITION_POSITION)
INTO last_partition
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = table_name;
FOR cur1 IN (SELECT PARTITION_NAME AS part2drop
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = table_name
AND PARTITION_POSITION > 1
AND PARTITION_POSITION <= last_partition - partition_number)
LOOP
stm := 'alter table ' || table_name|| ' drop partition "' || cur1.part2drop || '"';
EXECUTE IMMEDIATE stm;
END LOOP;
END;
END;
You can exec this procedure with job every day.
CodePudding user response：
I would suggest setting up a RETENTION table for each PARTITION table. Once the RETENTION period has passed then you can drop the PARTITION.
This should be a scheduled task. In addition, I found this code in stackoverflow, which renames system GENERATED PARTITION to something meaningful.
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;
/* test data */
CREATE TABLE t0 (
seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
dt DATE
)
PARTITION BY RANGE (dt)
INTERVAL
(NUMTOYMINTERVAL(1,'YEAR'))
(
PARTITION OLD_DATA values LESS THAN (TO_DATE('2020-01-01','YYYY-MM-DD'))
);
/
INSERT into t0 (dt)
with dt (dt, interv) as (
select date '2021-01-01', numtodsinterval(90,'DAY') from dual
union all
select dt.dt interv, interv from dt
where dt.dt interv < date '2028-12-31')
select dt from dt;
/
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-07-31')
select dt from dt;
/
INSERT into t1 (dt)
SELECT DATE '2022-01-01'
DBMS_RANDOM.VALUE(0, DATE '2022-01-31' - DATE '2022-01-01' 1)
FROM DUAL CONNECT BY LEVEL<=100;
/
create index t1_global_ix on t1 (dt);
/
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 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;
/
/* Schedule this task daily
rename system GENERATED PARTITION to something meaningful
*/
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;
/
/* schedule this task */
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;