Create table with Partition year and subpartition month In Oracle 12c

Question

I'm using Oracle 12c, I want to create a table with partition by year and subpartition by month. When I run the following code, it raise ORA-00922: missing or invalid option error. How can I fix it?

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)
);

My purpose is create a table which auto create partition each year and It save log in 12 months. If I add one more month, the last month will be deleted and this will be done by daly job. How can I do that?

Thanks!

Answer 1

I would suggest to create simple monthly partition. You can do it like this:

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')); 

New partitions will be added automatically. For maintenance you can create a procedure like below. You can run it by a daily job.

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;