Hi,

I'm testing a few Exchange Partition concepts to improve a process that I created.

The database version I'm using is:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

To test the solution that I though, I start creating the destination table TMP_TESTE_MANA_14:

CREATE TABLE TMP_TESTE_MANA_14
(
DT_ATUALIZACAO TIMESTAMP NOT NULL
)
PARTITION BY RANGE (DT_ATUALIZACAO)
(
PARTITION P_201810 VALUES LESS THAN (TO_DATE(' 2018-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

You can see the table has only one field (TIMESTAMP) and only one partition on the same field.

In the second step of my test, I created the source table TMP_TESTE_MANA_15:

CREATE TABLE TMP_TESTE_MANA_15
(
DT_ATUALIZACAO NOT NULL
)
AS
SELECT CAST(SYSTIMESTAMP AS TIMESTAMP) AS DT_ATUALIZACAO
FROM DUAL;

As you can see this table has the same number of fields, but has a value from the fucntion SYSTIMESTAMP with CAST() to TIMESTAMP.

In the last step I tried to do a simple Exchange Partition between these two tables:

ALTER TABLE TMP_TESTE_MANA_14 EXCHANGE PARTITION P_201810 WITH TABLE TMP_TESTE_MANA_15;

When I executed I got the follow error:

ORA-14097: column type or size mismatch on ALTER TABLE EXCHANGE PARTITION

I saw that through the follow query the fields have the same Data Type, but different DATA_LENGTH (11 and 20):

SELECT A.TABLE_NAME,
B.TABLE_NAME,
A.COLUMN_ID,
B.COLUMN_ID,
A.COLUMN_NAME,
B.COLUMN_NAME,
A.DATA_TYPE,
B.DATA_TYPE,
A.DATA_LENGTH,
B.DATA_LENGTH,
A.DATA_PRECISION,
B.DATA_PRECISION
FROM USER_TAB_COLUMNS A
FULL OUTER JOIN USER_TAB_COLUMNS B
ON (A.COLUMN_NAME = B.COLUMN_NAME)
WHERE A.TABLE_NAME = 'TMP_TESTE_MANA_14'
AND B.TABLE_NAME = 'TMP_TESTE_MANA_15';

TABLE_NAME TMP_TESTE_MANA_14
TABLE_NAME TMP_TESTE_MANA_15
COLUMN_ID 1
COLUMN_ID 1
COLUMN_NAME DT_ATUALIZACAO
COLUMN_NAME DT_ATUALIZACAO
DATA_TYPE TIMESTAMP(6)
DATA_TYPE TIMESTAMP(6)
DATA_LENGTH 11
DATA_LENGTH 20
DATA_PRECISION 
DATA_PRECISION 

I applied the same codes in Oracle Live SQL (version 18c), and worked. When I executed the FULL OUTER query in this environments both DATA_LENGTH have 11 bytes, like the CSV export bellow:

TABLE_NAME,TABLE_NAME,COLUMN_ID,COLUMN_ID,COLUMN_NAME,COLUMN_NAME,DATA_TYPE,DATA_TYPE,DATA_LENGTH,DATA_LENGTH,DATA_PRECISION,DATA_PRECISION
TMP_TESTE_MANA_14,TMP_TESTE_MANA_15,1,1,DT_ATUALIZACAO,DT_ATUALIZACAO,TIMESTAMP(6),TIMESTAMP(6),11,11, - , - 

Why in 11g version the CREATE TABLE and CREATE TABLE AS SELECT with CAST() have different number of bytes?

Is this a bug on 11g version? After all, works fine in 18c.

Could someone explain what is happening on 11g?

Is there a way to solve this situation on 11g?

Thanks.