OraFAQ Forum: SQL & PL/SQL » Partition & Sub partition Table Creation Error

Home » SQL & PL/SQL » SQL & PL/SQL » Partition & Sub partition Table Creation Error (Oracle 11.2.0.4)

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

Partition & Sub partition Table Creation Error [message #636660]

Thu, 30 April 2015 01:46

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi all,

Can you please help me with following Create table scripts?
I am trying to create new table with partition and sub partition as follows -

=====================================================================================
CREATE TABLE TEMP
(
ID NUMBER NOT NULL,
TYPE NUMBER,
NAME VARCHAR2(128 BYTE),
REV VARCHAR2(128 BYTE),
IND CHAR(2 BYTE) NOT NULL,
OPERATION_IND CHAR(2 BYTE) NOT NULL,
STATUS_IND CHAR(2 BYTE) NOT NULL,
CREATOR VARCHAR2(128 BYTE),
TIMESTAMP DATE,
O_TIMESTAMP DATE,
P_ID VARCHAR2(128 BYTE)
)
NOCOMPRESS
RESULT_CACHE (MODE DEFAULT)
NOLOGGING
PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
SUBPARTITION BY HASH( MONTH (TIMESTAMP) )
SUBPARTITIONS 12 (
PARTITION january VALUES LESS THAN (2),
PARTITION february VALUES LESS THAN (3),
PARTITION march VALUES LESS THAN (4),
PARTITION april VALUES LESS THAN (5),
PARTITION may VALUES LESS THAN (6),
PARTITION june VALUES LESS THAN (7),
PARTITION july VALUES LESS THAN ( 8 ),
PARTITION august VALUES LESS THAN (9),
PARTITION september VALUES LESS THAN (10),
PARTITION october VALUES LESS THAN (11),
PARTITION november VALUES LESS THAN (12),
PARTITION december VALUES LESS THAN (13))
( partition empty values less than(to_date('12/2011', 'MM/YYYY')))
PARALLEL ( DEGREE 8 INSTANCES 1 )
MONITORING;
=====================================================================================

It gives following error -

ERROR at line 19:
ORA-00907: missing right parenthesis

=====================================================================================

For your reference -
I have following requirement to create table -

"create table as Partition & Subpartition table on TIMESTAMP column This should be partition by year (interval), then subpartition by month"

Please help, Many thanks in advance.

Regards,

Hitesh

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636666 is a reply to message #636660]

Thu, 30 April 2015 02:00

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

You have a syntax error. However, I will not tell you what it is until you enclose your copy/paste within tags to make it readable. As described here,
How to use [code] tags and make your code easier to read
You have been asked to do this before.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636669 is a reply to message #636666]

Thu, 30 April 2015 02:29

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Sorry for the inconvenience,
Please find attached formatted script.

Please let me know for any correction.
Many Thanks in advance.


CREATE TABLE TEMP
(
	ID NUMBER NOT NULL,
	TYPE NUMBER,
	NAME VARCHAR2(128 BYTE),
	REV VARCHAR2(128 BYTE),
	IND CHAR(2 BYTE) NOT NULL,
	OPERATION_IND CHAR(2 BYTE) NOT NULL,
	STATUS_IND CHAR(2 BYTE) NOT NULL,
	CREATOR VARCHAR2(128 BYTE),
	TIMESTAMP DATE,
	O_TIMESTAMP DATE,
	P_ID VARCHAR2(128 BYTE)
)
	NOCOMPRESS
		RESULT_CACHE (MODE DEFAULT)
	NOLOGGING
PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
	SUBPARTITION BY HASH( MONTH (TIMESTAMP) )
	SUBPARTITIONS 12 (
		PARTITION january VALUES LESS THAN (2),
		PARTITION february VALUES LESS THAN (3),
		PARTITION march VALUES LESS THAN (4),
		PARTITION april VALUES LESS THAN (5),
		PARTITION may VALUES LESS THAN (6),
		PARTITION june VALUES LESS THAN (7),
		PARTITION july VALUES LESS THAN ( 8 ),
		PARTITION august VALUES LESS THAN (9),
		PARTITION september VALUES LESS THAN (10),
		PARTITION october VALUES LESS THAN (11),
		PARTITION november VALUES LESS THAN (12),
		PARTITION december VALUES LESS THAN (13)
			)
		( 
partition empty values less than(to_date('12/2011', 'MM/YYYY'))
		)
PARALLEL ( DEGREE 8 INSTANCES 1 )
MONITORING;

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636670 is a reply to message #636669]

Thu, 30 April 2015 02:34

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> CREATE TABLE TEMP
  2  (
  3     ID NUMBER NOT NULL,
  4     TYPE NUMBER,
  5     NAME VARCHAR2(128 BYTE),
  6     REV VARCHAR2(128 BYTE),
  7     IND CHAR(2 BYTE) NOT NULL,
  8     OPERATION_IND CHAR(2 BYTE) NOT NULL,
  9     STATUS_IND CHAR(2 BYTE) NOT NULL,
 10     CREATOR VARCHAR2(128 BYTE),
 11     TIMESTAMP DATE,
 12     O_TIMESTAMP DATE,
 13     P_ID VARCHAR2(128 BYTE)
 14  )
 15     NOCOMPRESS
 16             RESULT_CACHE (MODE DEFAULT)
 17     NOLOGGING
 18  PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
 19     SUBPARTITION BY HASH( MONTH (TIMESTAMP) )
 20     SUBPARTITIONS 12 (
 21             PARTITION january VALUES LESS THAN (2),
 22             PARTITION february VALUES LESS THAN (3),
 23             PARTITION march VALUES LESS THAN (4),
 24             PARTITION april VALUES LESS THAN (5),
 25             PARTITION may VALUES LESS THAN (6),
 26             PARTITION june VALUES LESS THAN (7),
 27             PARTITION july VALUES LESS THAN ( 8 ),
 28             PARTITION august VALUES LESS THAN (9),
 29             PARTITION september VALUES LESS THAN (10),
 30             PARTITION october VALUES LESS THAN (11),
 31             PARTITION november VALUES LESS THAN (12),
 32             PARTITION december VALUES LESS THAN (13)
 33                     )
 34             (
 35  partition empty values less than(to_date('12/2011', 'MM/YYYY'))
 36             )
 37  PARALLEL ( DEGREE 8 INSTANCES 1 )
 38  MONITORING;
        SUBPARTITION BY HASH( MONTH (TIMESTAMP) )
                                    *
ERROR at line 19:
ORA-00907: missing right parenthesis

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636672 is a reply to message #636670]

Thu, 30 April 2015 02:39

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Yes, it gives the same error every time, can you please help me with the solution?

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636673 is a reply to message #636672]

Thu, 30 April 2015 02:41

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

My requirement as follows -

"create table as Partition & Subpartition table on TIMESTAMP column This should be partition by Year(interval), then sub partition by Month"

Thanks in advance.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636674 is a reply to message #636673]

Thu, 30 April 2015 02:48

John Watson
Messages: 8988
Registered: January 2010
Location: Global Village

Senior Member

Think about your use of the MONTH. Is it going to work?

orclz>
orclz> select month(sysdate) from dual;
select month(sysdate) from dual
       *
ERROR at line 1:
ORA-00904: "MONTH": invalid identifier


orclz>

I haven't tried it, but EXTRACT might be better.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636677 is a reply to message #636673]

Thu, 30 April 2015 02:55

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

then sub partition by Month"

So not "SUBPARTITION BY HASH( " but BY LIST as you have 12 months in a year.
What is "MONTH (TIMESTAMP)"?

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636680 is a reply to message #636677]

Thu, 30 April 2015 04:10

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Oh ok, thanks for details, Now I have tried using LIST as subpartition as follows -


CREATE TABLE TEMP
(
   ID NUMBER NOT NULL,
   TYPE NUMBER,
   NAME VARCHAR2(128 BYTE),
   REV VARCHAR2(128 BYTE),
   IND CHAR(2 BYTE) NOT NULL,
   OPERATION_IND CHAR(2 BYTE) NOT NULL,
   STATUS_IND CHAR(2 BYTE) NOT NULL,
   CREATOR VARCHAR2(128 BYTE),
   TIMESTAMP DATE,
   O_TIMESTAMP DATE,
   P_ID VARCHAR2(128 BYTE)
)
   NOCOMPRESS
           RESULT_CACHE (MODE DEFAULT)
   NOLOGGING
PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
   SUBPARTITION BY LIST(TIMESTAMP)
(
partition empty values less than(to_date('12/2011', 'MM/YYYY'))
(
           SUBPARTITION january VALUES LESS THAN (2),
           SUBPARTITION february VALUES LESS THAN (3),
           SUBPARTITION march VALUES LESS THAN (4),
           SUBPARTITION april VALUES LESS THAN (5),
           SUBPARTITION may VALUES LESS THAN (6),
           SUBPARTITION june VALUES LESS THAN (7),
           SUBPARTITION july VALUES LESS THAN ( 8 ),
           SUBPARTITION august VALUES LESS THAN (9),
           SUBPARTITION september VALUES LESS THAN (10),
           SUBPARTITION october VALUES LESS THAN (11),
           SUBPARTITION november VALUES LESS THAN (12),
           SUBPARTITION december VALUES LESS THAN (13))
                   )
PARALLEL ( DEGREE 8 INSTANCES 1 )
MONITORING;

But now it gives this error -

ERROR at line 23:
ORA-00906: missing left parenthesis

Can you please help?

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636684 is a reply to message #636680]

Thu, 30 April 2015 04:15

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Use SQL*Plus and copy and paste what you do and get, like:

SQL> CREATE TABLE TEMP
  2  (
  3     ID NUMBER NOT NULL,
  4     TYPE NUMBER,
  5     NAME VARCHAR2(128 BYTE),
  6     REV VARCHAR2(128 BYTE),
  7     IND CHAR(2 BYTE) NOT NULL,
  8     OPERATION_IND CHAR(2 BYTE) NOT NULL,
  9     STATUS_IND CHAR(2 BYTE) NOT NULL,
 10     CREATOR VARCHAR2(128 BYTE),
 11     TIMESTAMP DATE,
 12     O_TIMESTAMP DATE,
 13     P_ID VARCHAR2(128 BYTE)
 14  )
 15     NOCOMPRESS
 16             RESULT_CACHE (MODE DEFAULT)
 17     NOLOGGING
 18  PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
 19     SUBPARTITION BY LIST(TIMESTAMP)
 20  (
 21  partition empty values less than(to_date('12/2011', 'MM/YYYY'))
 22  (
 23             SUBPARTITION january VALUES LESS THAN (2),
 24             SUBPARTITION february VALUES LESS THAN (3),
 25             SUBPARTITION march VALUES LESS THAN (4),
 26             SUBPARTITION april VALUES LESS THAN (5),
 27             SUBPARTITION may VALUES LESS THAN (6),
 28             SUBPARTITION june VALUES LESS THAN (7),
 29             SUBPARTITION july VALUES LESS THAN ( 8 ),
 30             SUBPARTITION august VALUES LESS THAN (9),
 31             SUBPARTITION september VALUES LESS THAN (10),
 32             SUBPARTITION october VALUES LESS THAN (11),
 33             SUBPARTITION november VALUES LESS THAN (12),
 34             SUBPARTITION december VALUES LESS THAN (13))
 35                     )
 36  PARALLEL ( DEGREE 8 INSTANCES 1 )
 37  MONITORING;
           SUBPARTITION january VALUES LESS THAN (2),
                                       *
ERROR at line 23:
ORA-00906: missing left parenthesis

If this is a LIST (sub)partitioning this is not the correct syntax to give values.
Please READ Database SQL Reference for the correct syntax.

Note: this is the last time I copy and paste what you should.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636692 is a reply to message #636684]

Thu, 30 April 2015 04:50

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Thanks, I did the changes and now able to create table using following scripts -

when I insert values for '2011', it allows to add, BUT when I insert values for '2012' it gives following error -

"ORA-14400: inserted partition key does not map to any partition"

So, can you please now let me know how partition / subpartition will automatically create as and when needed? (as I have already use INTERVAL), please suggest, Thanks in advance.


CREATE TABLE temp
(
  OID        NUMBER NOT NULL,
  TYPE       NUMBER,
  NAME       VARCHAR2(128 BYTE),
  REV        VARCHAR2(128 BYTE),
  RO_IND      CHAR(2 BYTE)  NOT NULL,
  OPERATION_IND  CHAR(2 BYTE)  NOT NULL,
  STATUS_IND     CHAR(2 BYTE)  NOT NULL,
  CREATOR        VARCHAR2(128 BYTE),
  TIMESTAMP  DATE,
  O_TIMESTAMP  DATE,
  ID         VARCHAR2(128 BYTE)
)
NOCOMPRESS
RESULT_CACHE (MODE DEFAULT)
NOLOGGING
PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
subpartition by list(TIMESTAMP)
SUBPARTITION TEMPLATE
   ( SUBPARTITION Jan VALUES (to_date('01/2011', 'MM/YYYY'))
   , SUBPARTITION Feb VALUES (to_date('02/2011', 'MM/YYYY'))
   , SUBPARTITION Mar VALUES (to_date('03/2011', 'MM/YYYY'))
   , SUBPARTITION Apr VALUES (to_date('04/2011', 'MM/YYYY'))
   , SUBPARTITION May VALUES (to_date('05/2011', 'MM/YYYY'))
   , SUBPARTITION Jun VALUES (to_date('06/2011', 'MM/YYYY'))
   , SUBPARTITION Jul VALUES (to_date('07/2011', 'MM/YYYY'))
   , SUBPARTITION Aug VALUES (to_date('08/2011', 'MM/YYYY'))
   , SUBPARTITION Sep VALUES (to_date('09/2011', 'MM/YYYY'))
   , SUBPARTITION Oct VALUES (to_date('10/2011', 'MM/YYYY'))
   , SUBPARTITION Nov VALUES (to_date('11/2011', 'MM/YYYY'))
   , SUBPARTITION Dec VALUES (to_date('12/2012', 'MM/YYYY'))
   )
( partition empty values less than(to_date('12/2011', 'MM/YYYY')))
PARALLEL ( DEGREE 8 INSTANCES 1 )
MONITORING;

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636693 is a reply to message #636692]

Thu, 30 April 2015 04:57

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

subpartition by list(TIMESTAMP)

This is wrong you want to subpartition by the month of timestamp not by timestamp.

In addition, your partition template should not refer the year. This is a template so the expression should be valid for any year.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636706 is a reply to message #636693]

Thu, 30 April 2015 09:26

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Many thanks for the details, its working fine now I made following changes also tried inserting the Rows for different Year's , its automatically creates the partition -

PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
subpartition by list(OID)  -- {As "OID" column is NUMBER so taking this, as TIMESTAMP not allow number, it requires date format}
SUBPARTITION TEMPLATE
   ( SUBPARTITION Jan VALUES (1)
   , SUBPARTITION Feb VALUES (2)
   , SUBPARTITION Mar VALUES (3)
   , SUBPARTITION Apr VALUES (4)
   , SUBPARTITION May VALUES (5)
   , SUBPARTITION Jun VALUES (6)
   , SUBPARTITION Jul VALUES (7)
   , SUBPARTITION Aug VALUES (8)
   , SUBPARTITION Sep VALUES (9)
   , SUBPARTITION Oct VALUES (10)
   , SUBPARTITION Nov VALUES (11)
   , SUBPARTITION Dec VALUES (12)
   )
( partition empty values less than(to_date('12/2011', 'MM/YYYY')))

Please let me know if still any issues.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636709 is a reply to message #636706]

Thu, 30 April 2015 09:54

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please post the COMPLETE statement.
How do you solve the problem "subpartition by month"?

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636742 is a reply to message #636709]

Fri, 01 May 2015 05:45

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

Hi,

Please find the script as follows -

CREATE TABLE test
(
  OID        NUMBER                         NOT NULL,
  TYPE       NUMBER,
  NAME       VARCHAR2(128 BYTE),
  REV        VARCHAR2(128 BYTE),
  RO_ID      CHAR(2 BYTE)                   NOT NULL,
  OPERATION_IND  CHAR(2 BYTE)                   NOT NULL,
  STATUS_IND     CHAR(2 BYTE)                   NOT NULL,
  CREATOR        VARCHAR2(128 BYTE),
  TIMESTAMP  DATE,
  O_TIMESTAMP  DATE,
  ID         VARCHAR2(128 BYTE)
)
NOCOMPRESS
NOLOGGING
PARTITION BY range(TIMESTAMP) interval(numtoyminterval(1, 'YEAR'))
subpartition by list(OID)
SUBPARTITION TEMPLATE
   ( SUBPARTITION Jan VALUES (1)
   , SUBPARTITION Feb VALUES (2)
   , SUBPARTITION Mar VALUES (3)
   , SUBPARTITION Apr VALUES (4)
   , SUBPARTITION May VALUES (5)
   , SUBPARTITION Jun VALUES (6)
   , SUBPARTITION Jul VALUES (7)
   , SUBPARTITION Aug VALUES (8)
   , SUBPARTITION Sep VALUES (9)
   , SUBPARTITION Oct VALUES (10)
   , SUBPARTITION Nov VALUES (11)
   , SUBPARTITION Dec VALUES (12)
   )
( partition empty values less than(to_date('12/2011', 'MM/YYYY')))
PARALLEL ( DEGREE 8 INSTANCES 1 )
MONITORING;

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636743 is a reply to message #636742]

Fri, 01 May 2015 05:47

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

I would like to say Many thanks Michel, as the issue resolved and also I learn new things about partition / sub partition, also it now creates partition / sub partition automatically as per the requirements and Year's Smile

Smile

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636751 is a reply to message #636743]

Fri, 01 May 2015 09:47

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But, unless OID is the month of your timestamp, it does not answer "subpartition by month".
You can do it using a virtual column:

SQL> CREATE TABLE test
  2  (
  3    OID        NUMBER                         NOT NULL,
  4    TYPE       NUMBER,
  5    NAME       VARCHAR2(128 BYTE),
  6    REV        VARCHAR2(128 BYTE),
  7    RO_ID      CHAR(2 BYTE)                   NOT NULL,
  8    OPERATION_IND  CHAR(2 BYTE)                   NOT NULL,
  9    STATUS_IND     CHAR(2 BYTE)                   NOT NULL,
 10    CREATOR        VARCHAR2(128 BYTE),
 11    TIMESTAMP  DATE,
 12  tim_month as (extract (month from timestamp)),
 13    O_TIMESTAMP  DATE,
 14    ID         VARCHAR2(128 BYTE)
 15  )
 16  NOCOMPRESS
 17  NOLOGGING
 18  PARTITION BY range (TIMESTAMP) interval (numtoyminterval(1, 'YEAR'))
 19  subpartition by list (tim_month)
 20  SUBPARTITION TEMPLATE
 21     ( SUBPARTITION Jan VALUES (1)
 22     , SUBPARTITION Feb VALUES (2)
 23     , SUBPARTITION Mar VALUES (3)
 24     , SUBPARTITION Apr VALUES (4)
 25     , SUBPARTITION May VALUES (5)
 26     , SUBPARTITION Jun VALUES (6)
 27     , SUBPARTITION Jul VALUES (7)
 28     , SUBPARTITION Aug VALUES (8)
 29     , SUBPARTITION Sep VALUES (9)
 30     , SUBPARTITION Oct VALUES (10)
 31     , SUBPARTITION Nov VALUES (11)
 32     , SUBPARTITION Dec VALUES (12)
 33     )
 34  ( partition empty values less than(to_date('12/2011', 'MM/YYYY')))
 35  PARALLEL ( DEGREE 8 INSTANCES 1 )
 36  MONITORING;

Table created.

Report message to a moderator

Re: Partition & Sub partition Table Creation Error [message #636794 is a reply to message #636751]

Sat, 02 May 2015 23:40

hitesh.bhatt
Messages: 84
Registered: February 2014
Location: INDIA

Member

oh ok, Many Thanks for information!

Report message to a moderator

Previous Topic:	Calc elapsed time per event & date
Next Topic:	How to convert a table of CLOBS to VARCHAR & How to come up with a magic number for any table that returns more than 32KB & ... ? (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Aug 31 04:02:31 CDT 2025