Creating partitioned table gets error

[Michael Chewnin]

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

[slimdave]

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

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 ...

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 ...

...
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 ...

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

[slimdave]

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.

[Michael Chewnin]

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