在Oracle 12c中使用分区年和子分区月创建表

本教程将介绍在Oracle 12c中使用分区年和子分区月创建表的处理方法,这篇教程是从别的地方看到的,然后加了一些国外程序员的疑问与解答,希望能对你有所帮助,好了,下面开始学习吧。

在Oracle 12c中使用分区年和子分区月创建表 教程 第1张

问题描述

我使用的是Oracle12c,我想创建一个按年分区和子分区按月的表。当我运行以下代码时,它引发ORA-00922:缺少或无效选项错误。我怎么修复它?

create table Log_his
(
  ID  NUMBER(20) not null,
  Year  number(4,0), -- Partition
  Monthlynumber(2,0)), -- Subpartition
  Log_reason VARCHAR2(5000),
  Log_detailVARCHAR2(5000),
 constraint PK_ID primary key (ID)
) PARTITION BY RANGE(Year) INTERVAL (1)
SUBPARTITION BY RANGE(Monthly)
SUBPARTITION TEMPLATE
(
SUBPARTITION January VALUES LESS THAN (02)tablespace MONTHLY_PARTITION,
SUBPARTITION February VALUES LESS THAN (03)tablespace MONTHLY_PARTITION,
SUBPARTITION March VALUES LESS THAN (04)tablespace MONTHLY_PARTITION,
SUBPARTITION April VALUES LESS THAN (05)tablespace MONTHLY_PARTITION,
SUBPARTITION May VALUES LESS THAN (06)tablespace MONTHLY_PARTITION,
SUBPARTITION June VALUES LESS THAN (07)tablespace MONTHLY_PARTITION,
SUBPARTITION July VALUES LESS THAN (08)tablespace MONTHLY_PARTITION,
SUBPARTITION August VALUES LESS THAN (09)tablespace MONTHLY_PARTITION,
SUBPARTITION September VALUES LESS THAN (10)tablespace MONTHLY_PARTITION,
SUBPARTITION October VALUES LESS THAN (11)tablespace MONTHLY_PARTITION,
SUBPARTITION November VALUES LESS THAN (12)tablespace MONTHLY_PARTITION,
SUBPARTITION December VALUES LESS THAN (MAXVALUE)
);

我目的是创建一个每年自动创建分区并在12个月内保存日志的表。如果我再增加一个月,最后一个月将被删除,这将由Daly JOB完成。我怎么才能做到这一点?

谢谢!

推荐答案

我建议创建简单的每月分区。您可以这样做:

create table Log_his
(
  ID  NUMBER(20) not null,
  Year  number(4,0), 
  Monthlynumber(2,0)), 
  Log_reason VARCHAR2(5000),-- Oracle default limit is VARCHAR2(4000) 
  Log_detailVARCHAR2(5000),
  PARTITION_KEY TIMESTAMP(0) GENERATED ALWAYS AS (TO_TIMESTAMP(year||'-'||Monthly, 'YYYY-MM')) VIRTUAL,
 constraint PK_ID primary key (ID)
) 
PARTITION BY RANGE (PARTITION_KEY) INTERVAL (INTERVAL '1' MONTH)
(PARTITION P_INITIAL VALUES LESS THAN (TIMESTAMP '2018-01-01 00:00:00')); 

将自动添加新分区。对于维护,您可以创建如下过程。您可以按日常作业运行它。

CREATE OR REPLACE PROCEDURE MaintainPartitions IS

 CANNOT_DROP_LAST_PARTITION EXCEPTION;
 PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

 sqlstr VARCHAR2(10000);
 ts TIMESTAMP;
 newName VARCHAR2(30);

 CURSOR TabPartitions IS
 SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
 FROM USER_TAB_PARTITIONS t
 WHERE TABLE_NAME = 'LOG_HIS'
 ORDER BY TABLE_NAME, PARTITION_POSITION;

BEGIN

 FOR aPart IN TabPartitions LOOP
  EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
  ts := ADD_MONTHS(ts, -1);
  newName := 'P_'||TO_CHAR(ts,'yyyy_fmmonth');
  IF aPart.PARTITION_NAME <> newName THEN 
sqlstr := 'ALTER TABLE '||aPart.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO '||newName;
EXECUTE IMMEDIATE sqlstr;
  END IF;
 END LOOP;

 FOR aPart IN TabPartitions LOOP
 BEGIN
  EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
  ts := ts - INTERVAL '1' DAY;
  IF ts < ADD_MONTHS(SYSDATE, -12) THEN
sqlstr := 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME||' UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE sqlstr;
  END IF;
 EXCEPTION
  WHEN CANNOT_DROP_LAST_PARTITION THEN
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION ('||aPart.PARTITION_NAME||') UPDATE GLOBAL INDEXES';
EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL (INTERVAL ''1'' MONTH)';
 END;
 END LOOP;

END MaintainPartitions; 

好了关于在Oracle 12c中使用分区年和子分区月创建表的教程就到这里就结束了,希望趣模板源码网找到的这篇技术文章能帮助到大家,更多技术教程可以在站内搜索。