Database Administration (MOSC)

MOSC Banner

List partitionning with subtr(column) : possible or not ?

edited Sep 8, 2013 11:21PM in Database Administration (MOSC) 6 commentsAnswered ✓
Hello,is it possible with 11.2.0.3 Enterprise Edition to create a list partition with a substring of a column (or a function applied on the column) ?
I always have ORA-00907: missing right parenthesis

For example :
CREATE TABLE testp
(
  SESSIONCODE         VARCHAR2(25 CHAR),
  EVENTDATE           DATE
)
NOCOMPRESS
TABLESPACE WORK
RESULT_CACHE (MODE DEFAULT)
PARTITION BY LIST (substr(SESSIONCODE,1,3) )
SUBPARTITION BY RANGE (EVENTDATE)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP_FR_0113 VALUES LESS THAN (TO_DATE('2013-01-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   SUBPARTITION SP_FR_0213 VALUES LESS THAN (TO_DATE('2013-02-25 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
   SUBPARTITION SP_FR_MAX  VALUES LESS THAN (MAXVALUE)

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center