Home » SQL & PL/SQL » SQL & PL/SQL » invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) (oracle db 11.2.0.1.0)
icon14.gif  invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) [message #661410] Thu, 16 March 2017 16:57 Go to next message
centaurux
Messages: 2
Registered: March 2017
Junior Member
Hi team,
I have an error when try to create the next table:

ORA-922 blink in interval option

CREATE TABLE test_table
(
STORE_ORDER_ID NUMBER(12) NOT NULL,
STATUS VARCHAR2(1 BYTE) DEFAULT 'U' NOT NULL,
STORE_ID NUMBER(12) NOT NULL,
SUPPLIER_ID NUMBER(10),
STOCKING_POINT_ID NUMBER(12),
SOURCE_TYPE VARCHAR2(1 BYTE) DEFAULT 'V' NOT NULL,
COMMODITY_ID NUMBER(10) NOT NULL,
PACK_SIZE NUMBER(11,5) NOT NULL,
DELIVERY_DATE DATE NOT NULL,
CASE_VOLUME NUMBER( 8 ),
RECEIVED_QTY NUMBER( 8 ),
IN_TRANSIT_QTY NUMBER( 8 ),
SUP_PERF_QTY NUMBER( 8 ),
HOLD_STATUS VARCHAR2(1 BYTE) NOT NULL,
WITHIN_DAY_SOURCE NUMBER(12),
RELEASE_RUN_ID NUMBER(12),
RELEASE_DATE DATE,
ORDER_NUMBER NUMBER(10),
VALIDATION_FAILURE_IND VARCHAR2(1 BYTE) DEFAULT 'N' NOT NULL,
GENERATION_METHOD NUMBER(1) DEFAULT 0 NOT NULL,
LAST_UPDATE_USERNAME VARCHAR2(16 BYTE),
TIMESTAMP NUMBER(38) NOT NULL,
RELEASE_WAVE NUMBER(2),
REPLAN_WAVE NUMBER(2),
LOAD_WAVE NUMBER(2),
ALLOC_IND VARCHAR2(4 BYTE) DEFAULT 'N' NOT NULL,
DELIVERY_NUMBER NUMBER(2) DEFAULT 1 NOT NULL
)
PARTITION BY RANGE (RELEASE_DATE)
SUBPARTITION BY LIST (SOURCE_TYPE)
SUBPARTITION TEMPLATE
(SUBPARTITION PURCHASE VALUES ('V') TABLESPACE RETAIL_DATA,
SUBPARTITION TRANSFER VALUES ('W') TABLESPACE RETAIL_DATA
)
INTERVAL( NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION FIRST_RELEASE_DATE VALUES LESS THAN (TO_DATE(' 1970-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
( SUBPARTITION FIRST_RELEASE_DATE_PURCHASE VALUES ('V'),
SUBPARTITION FIRST_RELEASE_DATE_TRANSFER VALUES ('W') ),
PARTITION VALUES LESS THAN (TO_DATE(' 2017-02-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-02-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-02-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-23 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-26 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-04-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-15 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-16 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-19 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2,
PARTITION VALUES LESS THAN (TO_DATE(' 2017-05-22 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
SUBPARTITIONS 2
)
NOCOMPRESS
NOCACHE
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
MONITORING
ENABLE ROW MOVEMENT;

Please can you help me. Embarassed

thank a lot
HHR
Re: invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) [message #661411 is a reply to message #661410] Thu, 16 March 2017 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) [message #661414 is a reply to message #661410] Thu, 16 March 2017 21:01 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
That code is full of bugs. Your INTERVAL clause is in the wrong place, it should be after the range clause. Then you have all those SUBPARTITIONS 2 clauses which apply only to hash partitions, you need to get rid of them.

Where did you get the code from?
Re: invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) [message #661444 is a reply to message #661414] Fri, 17 March 2017 13:10 Go to previous messageGo to next message
centaurux
Messages: 2
Registered: March 2017
Junior Member
Hi John

Great for your comments John, thank you a lot.

Is an export via TOAD from aip database oracle, is possible that toad export process has error.



Gracias
Regards

HHR
Re: invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) [message #661446 is a reply to message #661444] Fri, 17 March 2017 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
is possible that toad export process has error.
Without no doubts.
Use DBMS_METADATA.GET_DDL to "export" the table definition.
Or expdp/impdp.

Re: invalid option using INTERVAL( NUMTODSINTERVAL(1, 'DAY')) [message #661448 is a reply to message #661446] Fri, 17 March 2017 13:42 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
What is your oracle version. in the newer version you can just have it automatically make new partitions using the following command. As you insert data into the table, oracle will automatically create the new partitions that it needs.

CREATE TABLE TEST_TABLE
(
  STORE_ORDER_ID          NUMBER(12)            NOT NULL,
  STATUS                  VARCHAR2(1 BYTE)      DEFAULT 'U'                   NOT NULL,
  STORE_ID                NUMBER(12)            NOT NULL,
  SUPPLIER_ID             NUMBER(10),
  STOCKING_POINT_ID       NUMBER(12),
  SOURCE_TYPE             VARCHAR2(1 BYTE)      DEFAULT 'V'                   NOT NULL,
  COMMODITY_ID            NUMBER(10)            NOT NULL,
  PACK_SIZE               NUMBER(11,5)          NOT NULL,
  DELIVERY_DATE           DATE                  NOT NULL,
  CASE_VOLUME             NUMBER(8),
  RECEIVED_QTY            NUMBER(8),
  IN_TRANSIT_QTY          NUMBER(8),
  SUP_PERF_QTY            NUMBER(8),
  HOLD_STATUS             VARCHAR2(1 BYTE)      NOT NULL,
  WITHIN_DAY_SOURCE       NUMBER(12),
  RELEASE_RUN_ID          NUMBER(12),
  RELEASE_DATE            DATE,
  ORDER_NUMBER            NUMBER(10),
  VALIDATION_FAILURE_IND  VARCHAR2(1 BYTE)      DEFAULT 'N'                   NOT NULL,
  GENERATION_METHOD       NUMBER(1)             DEFAULT 0                     NOT NULL,
  LAST_UPDATE_USERNAME    VARCHAR2(16 BYTE),
  TIMESTAMP               NUMBER(38)            NOT NULL,
  RELEASE_WAVE            NUMBER(2),
  REPLAN_WAVE             NUMBER(2),
  LOAD_WAVE               NUMBER(2),
  ALLOC_IND               VARCHAR2(4 BYTE)      DEFAULT 'N'                   NOT NULL,
  DELIVERY_NUMBER         NUMBER(2)             DEFAULT 1                     NOT NULL
)
PARTITION BY RANGE (RELEASE_DATE)
INTERVAL( NUMTODSINTERVAL(1, 'DAY'))
SUBPARTITION BY LIST (SOURCE_TYPE)
SUBPARTITION TEMPLATE
  (SUBPARTITION PURCHASE VALUES ('V') TABLESPACE RETAIL_DATA,
   SUBPARTITION TRANSFER VALUES ('W') TABLESPACE RETAIL_DATA
  )
(  
  PARTITION FIRST_RELEASE_DATE VALUES LESS THAN (TO_DATE(' 1970-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (SUBPARTITION FIRST_RELEASE_DATE_PURCHASE VALUES ('V'),
   SUBPARTITION FIRST_RELEASE_DATE_TRANSFER VALUES ('W') )
);

[Updated on: Fri, 17 March 2017 13:48]

Report message to a moderator

Previous Topic: query dead slow
Next Topic: ORDER BY Clause error in cursor
Goto Forum:
  


Current Time: Thu Mar 28 16:30:55 CDT 2024