A Picture of a Syntax Diagram is Worth 1024 Words

I was moving some interval partitioned tables to a new tablespace and discovered some interesting omissions in the Oracle documentation, but eventually found the answer in the place I should have looked in first.

The “problematic” table

I use interval partitioning whenever possible when there’s a DATE column that is NOT NULL. This particular table’s DDL originally looked something like this (clauses not relevant to the issue are omitted):

CREATE TABLE "UPS_STATUS" 
   (	
    "STATUS_DT" DATE NOT NULL ENABLE, 
	"SOURCE" NUMBER(10,0), 
	"RECIP" NUMBER(10,0)
   ) 
  TABLESPACE "DW"
  PARTITION BY RANGE ("STATUS_DT") 
  INTERVAL (NUMTODSINTERVAL(1,'DAY')) STORE IN ("DW") 
 (PARTITION "P0"  VALUES LESS THAN 
   (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
       'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
    TABLESPACE "DW" ) ;

In the highlighted lines are the references to the destination tablespace for various pieces of the table. The tablespace on line 7 is used when adding partitions to a partitioned table and you don’t specify a tablespace; in this case, it’s an interval partitioned table, so you can’t manually add any partitions anyway. The tablespace on line 9 is where new interval partitions are created. Finally, the tablespace on line 13 is where the initial partition P0 is stored.

I wanted to move this table and all its partitions to a TDE-encrypted tablespace named DWTDE. Easy enough, the table didn’t have many partitions, so I ran these ALTER TABLE commands:

alter table ups_status modify default attributes tablespace dwtde;
alter table ups_status move partition p0 tablespace dwtde;
alter table ups_status move partition SYS_P133049 tablespace dwtde;
alter table ups_status move partition SYS_P133050 tablespace dwtde;

After I drop the DW tablespace, I check the DDL for UPS_STATUS and I see this:

CREATE TABLE "UPS_STATUS" 
   (	
    "STATUS_DT" DATE NOT NULL ENABLE, 
	"SOURCE" NUMBER(10,0), 
	"RECIP" NUMBER(10,0)
   ) 
  TABLESPACE "DWTDE"
  PARTITION BY RANGE ("STATUS_DT") 
  INTERVAL (NUMTODSINTERVAL(1,'DAY')) STORE IN ("DW") 
 (PARTITION "P0"  VALUES LESS THAN 
   (TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
       'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE 
    TABLESPACE "DWTDE" ) ;

On line 9, the STORE IN clause still specifies the DW tablespace which no longer exists. Usually DROP TABLESPACE will prevent the tablespace from being dropped when there are objects or references unless you use INCLUDING CONTENTS, but I was able to drop the tablespace anyway. The question is now: How do I change the STORE IN clause for interval partitioned tables?

Reading The Friendly Manual

Looking at the ALTER TABLE command in the 12.1.0.2 SQL Reference (the table was originally created on a 12.1.0.2 database), there was no reference to the STORE IN clause other than using it to add an IOT overflow segment.

Perhaps the documentation for 12.2.0.1 through 19c was updated; starting with 12.2.0.1, the STORE IN clause was finally mentioned on the ALTER TABLE page since automatic partitioning now includes LIST partitioning:

But there was still no reference to the STORE IN clause for interval partitioned tables in the alter_interval_partitioning section. It wasn’t until I decided to look at the syntax diagram that I found my answer:

Going back to the 12.1.0.2 documentation, the alter_interval_partitioning diagram was there after all but not explained in the body of the documentation. Making the last change to my table’s DDL was therefore very straightforward:

alter table ups_status set store in (dwtde);

Next Steps

In the future, I’ll look at the syntax diagrams first. Time to listen to Pictures at an Exhibition while reading some good graphic novels.