OraFAQ Forum: Performance Tuning » Error while creating partitioned table

Home » RDBMS Server » Performance Tuning » Error while creating partitioned table (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error while creating partitioned table [message #320003]

Tue, 13 May 2008 12:14

amorphous4u
Messages: 34
Registered: December 2007
Location: Boston, US

Member

Hi,
I am trying to create a table with composite partition.
The main partition is list and subpartition is range. During table creation I am getting error ORA-00922: missing or invalid option. I am not getting where I am wrong. Can anyone please help me?

The code is pasted below:

1 CREATE TABLE TEST2
2 (
3 TRANSACTION_ID NUMBER NOT NULL,
4 EVENT_DATETIME DATE,
5 CONTENT_TYPE VARCHAR2(20 BYTE),
6 ESERVER_SYNC_FLAG CHAR(1 BYTE),
7 UPDATE_FLAG CHAR(1 BYTE),
8 USER_GUID VARCHAR2(40 BYTE),
9 USER_GROUP_GUID VARCHAR2(40 BYTE),
10 FIRM_GUID VARCHAR2(40 BYTE),
11 CONTENT_CODE VARCHAR2(200 BYTE),
12 PROJECTTRACKINGCODE1 VARCHAR2(200 BYTE),
13 PROJECTTRACKINGCODE2 VARCHAR2(200 BYTE),
14 PROJECTTRACKINGCODE3 VARCHAR2(200 BYTE),
15 PROJECTTRACKINGCODE4 VARCHAR2(200 BYTE),
16 PROJECTTRACKINGCODE5 VARCHAR2(200 BYTE),
17 PROJECTTRACKINGCODE6 VARCHAR2(200 BYTE),
18 PROJECTTRACKINGCODE7 VARCHAR2(200 BYTE),
19 PROJECTTRACKINGCODE8 VARCHAR2(200 BYTE),
20 PROJECTTRACKINGCODE9 VARCHAR2(200 BYTE),
21 PROJECTTRACKINGCODE10 VARCHAR2(200 BYTE),
22 APPLICATIONID VARCHAR2(128 BYTE),
23 SUBAPPLICATIONID VARCHAR2(128 BYTE),
24 ESERVERSESSIONID VARCHAR2(40 BYTE),
25 USERID VARCHAR2(128 BYTE),
26 USERFNAME VARCHAR2(30 BYTE),
27 USERLNAME VARCHAR2(30 BYTE),
28 USERGROUPNAME VARCHAR2(128 BYTE),
29 DOMAIN VARCHAR2(128 BYTE),
30 FIRMCODE VARCHAR2(40 BYTE),
31 FIRMNAME VARCHAR2(128 BYTE),
32 FIRMTYPE VARCHAR2(128 BYTE),
33 SUBID VARCHAR2(128 BYTE),
34 ACCOUNTID VARCHAR2(128 BYTE),
35 INTERNAL_PRICE NUMBER(5,2) DEFAULT 1.00,
36 TRANSACTION_ID_GUID VARCHAR2(40 BYTE)
37 )
38 TABLESPACE PUTS_DATA
39 PARTITION BY LIST(CONTENT_TYPE)
40 SUBPARTITION BY RANGE (EVENT_DATETIME)
41 SUBPARTITION TEMPLATE(
42 SUBPARTITION UT_MAY_07 VALUES LESS THAN TO_DATE('2007-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
43 SUBPARTITION UT_JUN_07 VALUES LESS THAN TO_DATE('2007-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
44 SUBPARTITION UT_JUL_07 VALUES LESS THAN TO_DATE('2007-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
45 SUBPARTITION UT_AUG_07 VALUES LESS THAN TO_DATE('2007-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
46 SUBPARTITION UT_SEP_07 VALUES LESS THAN TO_DATE('2007-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
47 SUBPARTITION UT_OCT_07 VALUES LESS THAN TO_DATE('2007-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
48 SUBPARTITION UT_NOV_07 VALUES LESS THAN TO_DATE('2007-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
49 SUBPARTITION UT_DEC_07 VALUES LESS THAN TO_DATE('2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
50 SUBPARTITION UT_JAN_08 VALUES LESS THAN TO_DATE('2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
51 SUBPARTITION UT_FEB_08 VALUES LESS THAN TO_DATE('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
52 SUBPARTITION UT_MAR_08 VALUES LESS THAN TO_DATE('2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
53 SUBPARTITION UT_APR_08 VALUES LESS THAN TO_DATE('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),
54 SUBPARTITION UT_MAY_08 VALUES LESS THAN TO_DATE('2008-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
55 )
56 (
57 PARTITION CT_AU VALUES ('AutexUsage'),
58 PARTITION CT_DU VALUES ('DealsUsage'),
59 PARTITION CT_FU VALUES ('FilingsUsage'),
60 PARTITION CT_FDU VALUES ('FundamentalsUsage'),
61 PARTITION CT_OU VALUES ('OwnershipUsage'),
62 PARTITION CT_PU VALUES ('PrivateEquityUsage'),
63 PARTITION CT_RU VALUES ('ResearchUsage'),
64 PARTITION CT_TU VALUES ('TAUsage')
65 )
66* ENABLE ROW MOVEMENT;
SQL> /
SUBPARTITION BY RANGE (EVENT_DATETIME)
*
ERROR at line 40:
ORA-00922: missing or invalid option

Thanks and Regards,
Amit Verma

Report message to a moderator