DB Version: 12.1.0.2
I am trying to do an exchange partition operation but it failed with the error:
ERROR at line 1:
ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION
After the research I detected the presence of a hidden column, but I don't know why this column SYS_NC00036$ should be there:
SQL> select column_id, column_name, data_type, data_length, data_default, virtual_column, hidden_column from dba_tab_cols where table_name='CEL_FACTURA' order by 1;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH DATA_DEFAULT VIR HID
---------- ------------------------------ -------------------- ----------- ------------------------------ --- ---
1 CODIGO_COMPROBANTE NUMBER 22 NO NO
...
11 TOTAL_DESCUENTO NUMBER 22 NO NO
12 PROPINA NUMBER 22 0.00 NO NO
13 IMPORTE_TOTAL NUMBER 22 NO NO
14 MONEDA VARCHAR2 100 NO NO
15 FECHAP DATE 7 TO_DATE('1900/01/01','YYYY/MM/ NO NO
...
32 TOTAL_BASE_IMPONIBLE_REEMBOLSO NUMBER 22 NO NO
33 TOTAL_IMPUESTO_REEMBOLSO NUMBER 22 NO NO
34 VALOR_RETENCION_IVA NUMBER 22 NO NO
35 VALOR_RETENCION_RENTA NUMBER 22 NO NO
36 TIPO_FACTURA VARCHAR2 3 'NOR' NO NO
...
53 FECHA_TRANSACCION NUMBER 22 NO NO
54 TOTAL_SUBSIDIO NUMBER 22 0 NO NO
55 BASE_TARIFA_0 NUMBER 22 NO NO
SYS_NC00036$ RAW 126 NO YES <-- THIS COLUMN!!!!!
56 rows selected.
It seems like this column is due to a function based index but there is no function based index in there:
SQL> select * from dba_ind_expressions where index_name in (select index_name from dba_indexes where table_name = 'CEL_FACTURA');
no rows selected
SQL>
I executed the next query to see its contents:
08:22:13 prucel> select SYS_NC00036$, count(*) from CEL_FACTURA group by SYS_NC00036$;
SYS_NC00036$ COUNT(*)
------------------ ----------
55047156
03 838
01 1
3 rows selected.
I tried to drop the column but it is impossible:
SQL> alter table COMPROBANTES_ELECTRONICOS.CEL_FACTURA drop column SYS_NC00036$;
*
ERROR at line 1:
ORA-14148: DML and DDL operations are not directly allowed on the guard-column.
SQL> alter table COMPROBANTES_ELECTRONICOS.CEL_FACTURA set unused column SYS_NC00036$;
*
ERROR at line 1:
ORA-14148: DML and DDL operations are not directly allowed on the guard-column.
SQL> purge DBA_RECYCLEBIN;
DBA Recyclebin purged.
I also have to mention this table is partitioned.