Creating partitioned table gets error

When I submit the following script to create a partitioned tables I get an error message. Script:

Create table fact_logp (
VCH_ORIGIN VARCHAR2(255),
VCH_SOURCE VARCHAR2(100),
VCH_CATEGORY VARCHAR2(100),
N_EVENT NUMBER,
N_ERROR NUMBER,
VCH_USER VARCHAR2(255),
VCH_NODE VARCHAR2(128),
VCH_SERVER VARCHAR2(50),
DTDATE DATE,
LOGDATE DATE,
UTCDATE DATE,
N_HOURS NUMBER,
N_MINUTES NUMBER,
N_SECONDS NUMBER,
VCH_OBJECT1 VARCHAR2(255),
VCH_CLASS1 VARCHAR2(255),
VCH_ATTRIBUTE1 VARCHAR2(255),
VCH_VALUE1 VARCHAR2(255),
VCH_OBJECT2 VARCHAR2(255),
VCH_CLASS2 VARCHAR2(255),
VCH_ATTRIBUTE2 VARCHAR2(255),
N_GWREF NUMBER,
VCH_DESCRIPTION VARCHAR2(1000),
N_INSERTDATETIME DATE)
partition by range(DTDATE)
(partition JAN04 values less than (to_date('31-JAN-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition FEB04 values less than (to_date('29-FEB-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition MAR04 values less than (to_date('31-MAR-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition APR04 values less than (to_date('30-APR-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition MAY04 values less than (to_date('31-MAY-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition JUN04 values less than (to_date('30-JUN-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition JUL04 values less than (to_date('31-JUL-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition AUG04 values less than (to_date('31-AUG-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition SEP04 values less than (to_date('31-SEP-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition OCT04 values less than (to_date('31-OCT-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition NOV04 values less than (to_date('30-NOV-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS,
partition DEC04 values less than (to_date('31-DEC-2004 23:59:59','DD-MON-YYYY hh24:mi:ss')))
tablespace USERS
)
;

error message:ERROR at line 29:
ORA-14027: only one PARTITION clause may be specified

This is a 9i database, and examples in the Oracle DBA Handbook were used as models.

Thanks for any help.

Mike C
:confused:

The three parentheses at the end of each partition statement ought to be two ... ie .

Code:

---

partition by range(DTDATE)
(partition JAN04 values less than (to_date('31-JAN-2004 23:59:59','DD-MON-YYYY hh24:mi:ss'))
tablespace USERS,
partition FEB04 values less than (to_date('29-FEB-2004 23:59:59','DD-MON-YYYY hh24:mi:ss'))
tablespace USERS,
partition MAR04 values less than (to_date('31-MAR-2004 23:59:59','DD-MON-YYYY hh24:mi:ss'))
tablespace USERS,
...

---

Also, that partition naming convention is going to come back and bite you later . I'd go for partition names like 'Y2004_M01', 'Y2004_M02', ... 'Y2004_M12' so that they sort correctly if you wanted to do something like ...

Code:

---

select
  partition_name,
  num_rows
from
  user_tab_partitions
where
  table_name = 'FACT_LOGP'
order by 1
/

---

Also, the limits on the partitions are exclusive, so to be strict you ought to have ...

Code:

---

...
Partition Y2004_M01 Values Less Than (To_Date('01-02-2004','DD-MM-YYYY')),
Partition Y2004_M02 Values Less Than (To_Date('01-03-2004','DD-MM-YYYY')),
...

---

That would also avoid errors like thinking there are 31 days in September ;)

Lastly, if your partitions are all going in the same tablespace then just specify it once, like ...

Code:

---

...
  VCH_DESCRIPTION VARCHAR2(1000),
  N_INSERTDATETIME DATE)
tablespace USERS
partition by range(DTDATE)
(partition ...

---

Another point ... what's the story with the column naming convention? It's your own business, but I wouldn't be using those data type prefixes.

I'd also come up with a convention for abbreviating those column names, like DESC = DESCRIPTION, CTGRY or CAT = CATEGORY, OBJ = OBJECT etc.. Your finger joints will thank you one day.

Thank everyone so much for their input and help. I too am not an OCA or an OCP, but I hope to be one. I too am learning from the school of hard knock, I am a former IBM Senior Systems Programmer. I did RTFM
over and over for about two days before I posted the question. I guess I just missed a couple of the finer points.

Again thank everyone that responded.

Mike C

:)