oracle 数据库通过job定时删除表分区-ok

ksf泡面

已于 2022-07-09 16:27:11 修改

阅读量1.3k

点赞数

分类专栏： Oracle 分区表 oracle pl/sql 文章标签：数据库

于 2022-07-09 15:16:57 首次发布

本文链接：https://blog.csdn.net/u013737399/article/details/125693745

版权

Oracle 分区表同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

oracle pl/sql

1 篇文章 0 订阅

订阅专栏

通过job实现定时去调用一个删除分区的存储过程，完成分区删除。

以下步骤是删除分区的过程，希望能够帮到大家。

测试环境：11.2.0.4
需求：每天1点删除 test_part 表前3天前的分区。

创建测试环境 test_part 表，并插入数据。

drop table scott.test_part purge ;
CREATE TABLE test_part (create_time TIMESTAMP)
PARTITION BY RANGE (create_time) INTERVAL (NUMTODSINTERVAL(1,'DAY')) 
(PARTITION create_time_other  VALUES LESS THAN (TIMESTAMP' 2019-09-01 00:00:00')) ;
insert into scott.test_part values(to_date('2022-07-01 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-02 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-03 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-04 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-05 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-06 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-07 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-08 00:00:00','YYYY-MM-DD HH24:MI:SS'));
insert into scott.test_part values(to_date('2022-07-09 00:00:00','YYYY-MM-DD HH24:MI:SS'));
commit;

收集统计信息

begin
dbms_stats.gather_table_stats(
    ownname        =>'scott',
    tabname        =>'test_part',
    degree        =>4, 
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
    cascade        =>true,
    method_opt=>'for all columns size AUTO');
end;
/

检查分区信息及分区表的表空间

set lines 200 pages 200
set long 1000
col table_name for a30
col partition_name for a20
col partition_position for 9999
col tablespace_name for a20
col HIGH_VALUE format a85 wrapped
select table_name, partition_position,partition_name,tablespace_name,high_value,num_rows 
from dba_tab_partitions  
where TABLE_OWNER=upper('scott')
and table_name=upper('test_part')
order by partition_position;

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME       TABLESPACE_NAME      HIGH_VALUE                                                                              NUM_ROWS
------------------------------ ------------------ -------------------- -------------------- ------------------------------------------------------------------------------------- ----------
TEST_PART                                       1 CREATE_TIME_OTHER    USERS                TIMESTAMP' 2019-09-01 00:00:00'                                                                0
TEST_PART                                       2 SYS_P863             USERS                TIMESTAMP' 2022-07-02 00:00:00'                                                                1
TEST_PART                                       3 SYS_P864             USERS                TIMESTAMP' 2022-07-03 00:00:00'                                                                1
TEST_PART                                       4 SYS_P865             USERS                TIMESTAMP' 2022-07-04 00:00:00'                                                                1
TEST_PART                                       5 SYS_P866             USERS                TIMESTAMP' 2022-07-05 00:00:00'                                                                1
TEST_PART                                       6 SYS_P867             USERS                TIMESTAMP' 2022-07-06 00:00:00'                                                                1
TEST_PART                                       7 SYS_P868             USERS                TIMESTAMP' 2022-07-07 00:00:00'                                                                1
TEST_PART                                       8 SYS_P869             USERS                TIMESTAMP' 2022-07-08 00:00:00'                                                                1
TEST_PART                                       9 SYS_P870             USERS                TIMESTAMP' 2022-07-09 00:00:00'                                                                1
TEST_PART                                      10 SYS_P871             USERS                TIMESTAMP' 2022-07-10 00:00:00'                                                                1

10 rows selected.

编写删除过程。

--grant select on dba_objects to scoot ;
create or replace procedure table_drop_partition as
    table_owner varchar2(50); --存储拥有者
    table_name varchar2(100); --存储表名
    table_subobject_name varchar2(100); --存储分区表名
    v_SqlExec varchar2(200); --存储拼接后的语句
    cursor fetch_cursor is select owner,object_name,subobject_name,created 
                             from dba_objects 
                            where owner='SCOTT' 
                              and object_type ='TABLE PARTITION'
                              and GENERATED ='Y' --创建表时候创建的第一个分区（GENERATED ='N'）是不允许被删除
                              and object_name in('TEST_PART')
                              and created<=TRUNC(SYSDATE+3);
begin
    for get_cursor in fetch_cursor loop
        table_owner:=get_cursor.OWNER;
        table_name:=get_cursor.OBJECT_NAME;
        table_subobject_name:=get_cursor.SUBOBJECT_NAME;
        
        --拼接删除分区语句
        v_SqlExec:='alter table '|| table_owner ||'.'||table_name||' DROP PARTITION '||table_subobject_name;
        
        --打印语句
        DBMS_OUTPUT.PUT_LINE('删除分区'||v_SqlExec);
        
        --执行语句
        execute immediate v_SqlExec;
    end loop;
end;
/

手工调用存储过程

set serveroutput on ;
set lines 200 pages 200 ;
exec table_drop_partition();

输出结果

删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P863
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P864
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P865
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P866
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P867
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P868
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P869
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P870
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P871

PL/SQL procedure successfully completed.

遇到的问题：
如果出现输出结果中有“????????????”，如下：
????????????alter table EHY.EM_REQUESTLOG DROP PARTITION SYS_P812
????????????alter table EHY.EM_REQUESTLOG DROP PARTITION SYS_P813
????????????alter table EHY.EM_REQUESTLOG DROP PARTITION SYS_P814
????????????alter table EHY.EM_REQUESTLOG DROP PARTITION SYS_P815
????????????alter table EHY.EM_REQUESTLOG DROP PARTITION SYS_P816

PL/SQL procedure successfully completed.

解决：设置环境变量，并且客户端工具的字符集也设置为UTF-8后重新创建该存储过就好了
export NLS_LANG=AMERICAN_AMERICA.UTF8

检查分区信息及分区表的表空间

set lines 200 pages 200
set long 1000
col table_name for a30
col partition_name for a20
col partition_position for 9999
col tablespace_name for a20
col HIGH_VALUE format a85 wrapped
select table_name, partition_position,partition_name,tablespace_name,high_value,num_rows 
from dba_tab_partitions  
where TABLE_OWNER=upper('scott')
and table_name=upper('test_part')
order by partition_position;

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME       TABLESPACE_NAME      HIGH_VALUE                                                                              NUM_ROWS
------------------------------ ------------------ -------------------- -------------------- ------------------------------------------------------------------------------------- ----------
TEST_PART                                       1 CREATE_TIME_OTHER    USERS                TIMESTAMP' 2019-09-01 00:00:00'                                                                0

调整存储过程-动态赋值

grant select on dba_objects to scoot ;
create or replace procedure table_drop_partition(table_owner varchar2,table_name varchar2,keep_days in number) as
    --table_owner varchar2(50); --存储拥有者
    --table_name varchar2(100); --存储表名
    table_subobject_name varchar2(100); --存储分区表名
    v_SqlExec varchar2(200); --存储拼接后的语句
    cursor fetch_cursor is select owner,object_name,subobject_name,created 
                             from dba_objects 
                            where owner=upper(table_owner)
                              and object_type ='TABLE PARTITION'
                              and GENERATED ='Y' --创建表时候创建的第一个分区（GENERATED ='N'）是不允许被删除
                              and object_name in( upper(table_name) )
                              and created<=TRUNC( SYSDATE + keep_days );
    --v_seg fetch_cursor%rowtype;
    
begin
    for get_cursor in fetch_cursor loop
        --table_owner:=get_cursor.OWNER;
        --table_name:=get_cursor.OBJECT_NAME;
        table_subobject_name:=get_cursor.SUBOBJECT_NAME;
        
        --拼接删除分区语句
        v_SqlExec:='alter table '|| table_owner ||'.'||table_name||' DROP PARTITION '||table_subobject_name;
        
        --打印语句
        DBMS_OUTPUT.PUT_LINE('删除分区'||v_SqlExec);
        
        --执行语句
        execute immediate v_SqlExec;
    end loop;
end;
/

手工调用存储过程

set serveroutput on ;
set lines 200 pages 200 ;
exec table_drop_partition('SCOTT','TEST_PART',5);

输出结果

删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P872
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P873
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P874
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P875
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P876
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P877
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P878
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P879
删除分区alter table SCOTT.TEST_PART DROP PARTITION SYS_P880

PL/SQL procedure successfully completed.

检查分区信息及分区表的表空间

set lines 200 pages 200
set long 1000
col table_name for a30
col partition_name for a20
col partition_position for 9999
col tablespace_name for a20
col HIGH_VALUE format a85 wrapped
select table_name, partition_position,partition_name,tablespace_name,high_value,num_rows 
from dba_tab_partitions  
where TABLE_OWNER=upper('scott')
and table_name=upper('test_part')
order by partition_position;

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME       TABLESPACE_NAME      HIGH_VALUE                                                                              NUM_ROWS
------------------------------ ------------------ -------------------- -------------------- ------------------------------------------------------------------------------------- ----------
TEST_PART                                       1 CREATE_TIME_OTHER    USERS                TIMESTAMP' 2019-09-01 00:00:00'                                                                0

创建job

第一种：使用dbms_job

--调用存储过程：table_drop_partition
var job_num number;
begin
    dbms_job.submit(JOB  => :job_num,
                    WHAT => 'scott.table_drop_partition;',
                    NEXT_DATE => to_date('2022-07-10 01:00:00','yyyy-mm-dd hh24:mi:ss'),
                    INTERVAL =>'TRUNC(sysdate+1)+(1/24)'
                    );
    commit ;
end;
/

--或者调用存储过程：table_drop_partition('SCOTT','TEST_PART',5)
var job_num number;
begin
    dbms_job.submit(JOB  => :job_num,
                    WHAT => 'scott.table_drop_partition('SCOTT','TEST_PART',5);',
                    NEXT_DATE => to_date('2022-07-10 01:00:00','yyyy-mm-dd hh24:mi:ss'),
                    INTERVAL =>'TRUNC(sysdate+1)+(1/24)'
                    );
    commit ;
end;
/

第二种：使用dbms_scheduler

添加定时任务，每天1 点调用。

--调用存储过程：table_drop_partition
BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
   job_name => 'AUTO_DROP_PARTITION_JOB',
   job_type => 'PLSQL_BLOCK',
   job_action => 'table_drop_partition;',
   start_date => to_date('2022-07-10 01:00:00','yyyy-mm-dd hh24:mi:ss'),
   repeat_interval => 'TRUNC(sysdate+1)+1/24');
END;
/

--或者调用存储过程：table_drop_partition('SCOTT','TEST_PART',5)
BEGIN
 DBMS_SCHEDULER.CREATE_JOB(
   job_name => 'AUTO_DROP_PARTITION_JOB',
   job_type => 'PLSQL_BLOCK',
   job_action => 'table_drop_partition('SCOTT','TEST_PART',5);',
   start_date => to_date('2022-07-10 01:00:00','yyyy-mm-dd hh24:mi:ss'),
   repeat_interval => 'TRUNC(sysdate+1)+1/24');
END;
/

检查job是否添加成功

select * from user_scheduler_jobs ;

手工调用job，检查job是否能够调用 delPart 过程，并删除分区。

begin
  dbms_scheduler.run_job('AUTO_DROP_PARTITION_JOB') ;
end;

检查job调用日志

select * from dba_scheduler_job_log where job_name='AUTO_DROP_PARTITION_JOB';

检查分区是否删除。

select OWNER,OBJECT_NAME,SUBOBJECT_NAME,CREATED,created
 from dba_objects 
where owner='SCOTT' 
  and  object_type ='TABLE PARTITION'
  and GENERATED ='Y' 
  and object_name in('TEST_PART')
  and created<=TRUNC(SYSDATE+3);