本文介紹了在Oracle 12c中使用分區年和子分區月創建表的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！
問題描述
我使用的是Oracle12c，我想創建一個按年分區和子分區按月的表。當我運行以下代碼時，它引發ORA-00922：缺少或無效選項錯誤。我如何修復它？
create table Log_his
(
ID NUMBER(20) not null,
Year number(4,0), -- Partition
Monthly number(2,0)), -- Subpartition
Log_reason VARCHAR2(5000),
Log_detail VARCHAR2(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),
Monthly number(2,0)),
Log_reason VARCHAR2(5000),-- Oracle default limit is VARCHAR2(4000)
Log_detail VARCHAR2(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中使用分區年和子分區月創建表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，
