Oracle通过procedure定时删除旧分区
1.现状
表为自动分区表，按照天进行分区。
每天有资料插入，每天自动生成一个新的分区。
该表按照业务需要仅需要保存三天的资料量。
CREATE TABLE MESPPT.BC_RET_PROC_DATA_HIS
(
BC_NAME VARCHAR2(10 CHAR),
DB_ADDR VARCHAR2(15 CHAR),
EVT_SEQ_ID VARCHAR2(32 CHAR),
NODE_NO VARCHAR2(2 CHAR),
TOOL_ID VARCHAR2(16 CHAR),
TAL_PAGE NUMBER,
CUR_PAGE NUMBER,
REPORT_ID VARCHAR2(60 CHAR),
RECIPE_ID VARCHAR2(40 CHAR),
RECIPE_ID_CHECK VARCHAR2(1 CHAR),
RECIPE_BODY_CHECK VARCHAR2(1 CHAR),
PROC_TIME NUMBER,
PROC_START TIMESTAMP(6),
PROC_END TIMESTAMP(6),
ESD_POS_F VARCHAR2(2 CHAR),
ESD_POS_S VARCHAR2(2 CHAR),
ESD_RADIO_F NUMBER,
ESD_RADIO_S NUMBER,
ESD_VAL_F NUMBER,
ESD_VAL_S NUMBER,
DATA_EXT1 VARCHAR2(30 CHAR),
DATA_EXT2 VARCHAR2(30 CHAR),
DATA_EXT3 VARCHAR2(60 CHAR),
DATA_EXT4 VARCHAR2(30 CHAR),
DATA_EXT5 VARCHAR2(30 CHAR),
EVT_TIMESTAMP TIMESTAMP(6) NOT NULL,
SRC_DB_TIMESTAMP TIMESTAMP(6),
DB_TIMESTAMP TIMESTAMP(6) DEFAULT SYSTIMESTAMP
)
NOCOMPRESS
TABLESPACE TBS32K02_SAS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
PARTITION BY RANGE (EVT_TIMESTAMP)
INTERVAL (NUMTODSINTERVAL(1,'DAY'))
(
PARTITION VALUES LESS THAN (TIMESTAMP' 2024-10-14 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE TBS32K02_SAS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (TIMESTAMP' 2024-10-15 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE TBS32K02_SAS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (TIMESTAMP' 2024-10-16 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE TBS32K02_SAS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION VALUES LESS THAN (TIMESTAMP' 2024-10-17 00:00:00')
LOGGING
NOCOMPRESS
TABLESPACE TBS32K02_SAS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
)
NOCACHE;
CREATE INDEX MESPPT.BC_RET_PROC_DATA_HIS_IDX01 ON MESPPT.BC_RET_PROC_DATA_HIS
(TOOL_ID, REPORT_ID)
TABLESPACE TBSIDX32K02_SAS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
LOCAL (
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
);
CREATE INDEX MESPPT.BC_RET_PROC_DATA_HIS_SRCDBTIME ON MESPPT.BC_RET_PROC_DATA_HIS
(SRC_DB_TIMESTAMP)
TABLESPACE TBSIDX32K02_SAS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
BUFFER_POOL DEFAULT
)
LOGGING
LOCAL (
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
),
PARTITION
LOGGING
NOCOMPRESS
TABLESPACE TBSIDX32K02_SAS
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
BUFFER_POOL DEFAULT
)
);
2.编写procedure
CREATE OR REPLACE procedure mesppt.drop_bc_partition
as
BEGIN
FOR v_sql IN(
select object_name,subobject_name,created ,'alter table ' ||'MESPPT'||'.'||object_name||' DROP PARTITION '||subobject_name||' UPDATE GLOBAL INDEXES' AS sql_txt
from USER_objects
where
--owner='mesppt'
--and
object_type ='TABLE PARTITION'
and GENERATED ='Y' --创建表时候创建的第一个分区（GENERATED ='N'）是不允许被删除
and object_name in('BC_RET_PROC_DATA_HIS')
and created<=(sysdate-4)
)
LOOP
dbms_output.put_line(v_sql.sql_txt);
EXECUTE IMMEDIATE v_sql.sql_txt;
END LOOP;
END; 3.定时JOB
DECLARE
NewJobID NUMBER;
USER_NAME VARCHAR2(128);
BEGIN
select user into user_name from dual;
execute immediate 'alter session set current_schema = MESPPT';
SYS.DBMS_JOB.SUBMIT
( job => NewJobID
,what => 'MESPPT.BC_PROCESS_DATA_CONFIG_TRUNCATE;'
,next_date => TO_DATE('2024/10/16 10:00:00', 'YYYY/MM/DD HH24:MI:SS')
,interval => 'TRUNC(SYSDATE+1)+10/24'
,no_parse => FALSE);
COMMIT;
execute immediate 'alter session set current_schema = ' || user_name || '';
EXCEPTION
WHEN OTHERS THEN
execute immediate 'alter session set current_schema = ' || user_name || '';
RAISE;
END;
/