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 - ProductionTo 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 PARTITIONI 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.
There is a bug with cast ( ... as timestamp ). In 11.2x is always sets the length to 20 when you use it in create table.
This is fixed in 12.2. See MOS note 19613048.8.