Oracle创建按时间戳分区表

最新推荐文章于 2025-06-13 09:00:00 发布

原创最新推荐文章于 2025-06-13 09:00:00 发布 · 3.4k 阅读

1 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

7 篇文章

订阅专栏

本文介绍如何在Oracle中创建范围分区表，并通过存储过程实现对新增分区的自动重命名，最后设置定时任务定期调用该存储过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 建分区表

-- Create table
create table PARTITION_TABLE
(
  field1   INTEGER,
  field2   VARCHAR2(32),
  field3   INTEGER,
)
partition by range(field3)
interval (86400)
(partition p1 values less than (1431964800) ) nologging
tablespace user;
);

2 增加存储过程对扩展的分区改名

CREATE OR REPLACE PROCEDURE PRO_RENAME_PARTITION AS
  SQLSTR VARCHAR2(4000);
  val2 int;
  newpart varchar2(32);
BEGIN
  for REC in (select table_name
                from user_tables
               where table_name like 'PARTITION_%') loop
    for x in (select high_value, partition_name
                from user_tab_partitions
               where table_name = REC.TABLE_NAME
                 and partition_name not like 'PART_201%') loop
      val2    := to_number(x.high_value);
      newpart := 'PART_' ||
                 to_char(to_date('19700101080000', 'yyyymmddhh24miss') +
                         (val2 - 3600) / 86400,
                         'yyyymmdd');
      sqlstr  := 'alter table ' || REC.TABLE_NAME || ' rename partition ' ||
                 x.partition_name || ' to ' || newpart;
      begin
        execute immediate sqlStr;
      exception
        when others then
          null;
      end;
    end loop;
  end loop;
END PRO_RENAME_PARTITION;

3 增加job定时调用存储过程

/
begin 
dbms_scheduler.create_job 
( 
job_name => 'job_pro_rename_partition', 
job_type => 'PLSQL_BLOCK', 
job_action => 'begin PRO_RENAME_PARTITION ; end;',
repeat_interval => 'FREQ=DAILY;BYHOUR=1;byminute=0', 
enabled => true
); 
end; 
/