Observations on an Oracle bug: You can't CTAS an IOT with Timestamp(n)!

If you are planning on loading data into a partitioned IOT with a "Timestamp(n)" field using CTAS, well, you can't!

I came across the issue a while ago, and had it confirmed by Oracle as a bug, which manifests itself even in 12cR1.

Here is the scenario:

You would require to load an IOT (named EMP) by adding new partitions and exchanging with another IOT that's produced using a CTAS.

This is basically what you would need to do:

CREATE TABLE EMP(
DeptID NUMBER,
EmpID   NUMBER,
HireDate TIMESTAMP(3),
CONSTRAINT pk_emp PRIMARY KEY(DeptID, EmpID))
 ORGANIZATION INDEX COMPRESS 1
 PARTITION BY LIST(DeptID)
 (PARTITION DEPT_0 VALUES(0));

CREATE TABLE Temp(
DeptID,
EmpID,
HireDate,
CONSTRAINT pk_temp PRIMARY KEY(DeptID, EmpID)) ORGANIZATION INDEX COMPRESS 1 AS
SELECT 1,1,CAST(SYSTIMESTAMP AS TIMESTAMP(3))
FROM dual;

ALTER TABLE Emp ADD PARTITION DEPT_1 VALUES(1);

ALTER TABLE Emp EXCHANGE PARTITION DEPT_1 WITH TABLE Temp;

And That's when you're hit in the face by a:

SQL Error: ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
14097. 00000 -  "column type or size mismatch in ALTER TABLE EXCHANGE PARTITION"
*Cause:    The corresponding columns in the tables specified in the
           ALTER TABLE EXCHANGE PARTITION are of different type or size
*Action:   Ensure that the two tables have the same number of columns
           with the same type and size.

Obviously you will check:

desc emp
Name     Null     Type      
-------- -------- ------------
DEPTID   NOT NULL NUMBER    
EMPID    NOT NULL NUMBER    
HIREDATE          TIMESTAMP(3)

desc temp
Name     Null     Type      
-------- -------- ------------
DEPTID   NOT NULL NUMBER    
EMPID    NOT NULL NUMBER    
HIREDATE          TIMESTAMP(3)

To prove that it is actually possible to exchange partition with the IOT:

CREATE TABLE Temp1(
DeptID NUMBER,
EmpID   NUMBER,
HireDate TIMESTAMP(3),
CONSTRAINT pk_temp1 PRIMARY KEY(DeptID, EmpID))
 ORGANIZATION INDEX COMPRESS 1;

INSERT INTO Temp1 VALUES(1,2,SYSTIMESTAMP);
COMMIT;

ALTER TABLE Emp EXCHANGE PARTITION DEPT_1 WITH TABLE Temp1;

And you're politely made aware of the fact that:

"table EMP altered."

What's going on here?

Well, the following simple query would shine enough light on the issue:

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_ID, DATA_TYPE, DATA_LENGTH, DATA_SCALE
FROM USER_TAB_COLS
WHERE TABLE_NAME IN ('EMP','TEMP','TEMP1')
ORDER BY 3,1;

TABLE_NAME COLUMN_NAME  COLUMN_ID DATA_TYPE    DATA_LENGTH
---------- ----------- ---------- ------------ -----------
EMP        DEPTID               1 NUMBER                22
TEMP       DEPTID               1 NUMBER                22
TEMP1      DEPTID               1 NUMBER                22
EMP        EMPID                2 NUMBER                22
TEMP       EMPID                2 NUMBER                22
TEMP1      EMPID                2 NUMBER                22
EMP        HIREDATE             3 TIMESTAMP(3)          11
TEMP       HIREDATE             3 TIMESTAMP(3)          20
TEMP1      HIREDATE             3 TIMESTAMP(3)          11 

As you can see CAST(column as TIMESTAMP(n)) would merely manifest itself as a TIMESTAMP(n) but in fact, it's not different than a "TIMESTAMP!"

Solution:

Modify the source of your CTAS operation to actually hold a "TIMESTAMP(n)" value, and get rid of the CAST function. Although the function works for other data types.