Creando tablas particionadas y error ORA-00922

Hay muchas versiones diferentes del software de Oracle Database y aún si la lógica y operaciones de la base de datos es más o menos la misma entre versiones, también es más o menos diferente entre versiones; por ejemplo, no hay sentencia drop database en Oracle 9i.

Entonces tu trabajas usualmente con bases de datos 11g y te encantan las tablas particionadas por intervalos, y creas una:

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:43:42 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);

Table created.

E intentas crear esa misma tabla en una base de datos 10g sólo para encontrar que no puedes:

oracle@my10gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Feb 8 12:46:15 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
);
INTERVAL (NUMTODSINTERVAL(1,'day'))
*
ERROR at line 7:
ORA-00922: missing or invalid option

No busques más: el error ORA-00922 en una base de datos 10g significa que estás intentando usar una funcionalidad que no existe; en una base de datos 11g podría significar falta de paréntesis o errores de sintaxis.

oracle@my11gdb~$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Wed Feb 8 12:50:54 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE MY_TABLE
(   
SOMETEXT     VARCHAR2(4000),
MOREINFO     VARCHAR2(256),
ADATE        DATE
) PARTITION BY RANGE (ADATE)
INTERVAL (NUMTODSINTERVAL(1,'day'))
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'));
partition p0 values less than (to_date('01-jan-2000','dd-mon-yyyy'))
*
ERROR at line 8:
ORA-00922: missing or invalid option

Más información:

CREATE TABLE (10g Release 2)
CREATE TABLE (11g Release 1)
Partition Administration