Interval Partitioned Table Receiving Ora-00959 Error When Trying To Create A New Partition (Doc ID 1339654.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

Insert into a new month in an interval partitioned table gives ORA-00959:

SQL> Insert into DBA_NWARCHIVE2.VDS_DATA
2 (DEVICE_ID, DETECTOR_ID, TIMESTAMP, STATUS, VOLUME, SPEED, OCCUPANCY, CONFIDENCE, PASSENGER, TWO_AXLE, THREE_AXLE_TRUCK)
3 Values
4 (1234, 5678, TO_DATE('08/01/2011 12:34:56', 'MM/DD/YYYY HH24:MI:SS'), 1, 7,
5 123, 4, 567, 8,
6 1, 2);
Insert into DBA_NWARCHIVE2.VDS_DATA
*
ERROR at line 1:
ORA-00959: tablespace 'ATMS_RW_128M' does not exist

Changes

The table called VDS_DATA was created in atms_rw_128m tablespace with interval partitioning.

DDL of the table VDS_DATA shows that the STORE IN clause was specified for the interval partition data.

PARTITION BY RANGE ("TIMESTAMP") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) STORE IN ("ATMS_RW_128M")

After modifying the default attributes of the table & index and then moving the partitions to a new tablespace (ATMS_128M) the original tablespace atms_rw_128m was dropped.

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms