0

I'm using Oracle 12c, I want to create a table with partition by year and subpartition by month like this.

create table par.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)
);

When I run it, it raise ORA-00922: missing or invalid option error. How can I fix it ?

My purpose is create a table with auto create partition each year and It save log in 12 month. If I add one more month, the last month will be deleted and this will be done by daly job. Can I define the last month and delete it ?

Thanks!

  • 1. create and post a Minimal, Complete, Reproducible Example. Maybe you will found the error without further help – miracle173 Dec 24 '19 at 21:41
  • 2. if it is possible use a tool like sqlplus to execute a command. Usually it will show you a line and the column number of the position where the error occurs, – miracle173 Dec 24 '19 at 21:43
  • Get rid of extra right parenthesis on Monthly and varchar2 max limit is 4000 characters – Sam Dec 25 '19 at 4:48
  • @miracle173 I gave the error above. It position at the end of script – Peace Peace Dec 25 '19 at 7:19
  • @Sam Thanks, I changed it – Peace Peace Dec 25 '19 at 7:19
1

You need to specify where partition starts from i.e Year

create table par.Log_his
(
  ID     NUMBER(20) not null,-- Partition
  Year           number(4,0), -- Subpartition
  Monthly            number(2,0), 
  Log_reason                VARCHAR2(4000),
  Log_detail               VARCHAR2(4000),
  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) 
)
(
PARTITION pstart VALUES LESS THAN (2019) -- or more partition as per your requirement
)
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.