Dear Tom

during a load phase of a table that has a clob I use an "alter table exchange partition" instruction from a stage table and sometimes (and only sometimes, not everytime) during an execution of a view (that insists on another partition of this table, not affected by the ddl) from a front-end application I get in a unpredictable manner the ora-14766 error.

My question is:
why do this not happen everytime I run the DDL instruction?

The view takes a long execution time and it's not reasonable to manage the exception and rerun again the view.


These are some info to understand better my question:

desc TNXRRAPT

----------------------------------------------------------------------------------------- -------- ------------------------------------------------------------
PARTITION NOT NULL CHAR(30)
AZIENDA NOT NULL CHAR(5)
DATA_RIFERIMENTO NOT NULL CHAR(8)
NDG NOT NULL CHAR(16)
SPORTELLO NOT NULL CHAR(7)
CONTO NOT NULL CHAR(32)
CD_TITOLO NOT NULL CHAR(12)
PROGR NOT NULL NUMBER(6)
TCH_DIRETT NOT NULL CHAR(11)
NDG_RAPP NOT NULL CHAR(16)
TCH_MODELLO NOT NULL CHAR(4)
L3FLSOUT NOT NULL NUMBER(2)
ZONA_VAR NOT NULL CLOB



select partition,sum(1) from TNXRRAPT group by partition order by 1;


0103020170630001STAR 189671
0103020170630001TGET 189679
0103020171231001STAR 180208
0103020171231001TGET 180214
LOM20161231001STAR 1458894
LOM20161231001TGE2 1458894


create table TNXRRAPT_A as select * from TNXRRAPT partition(P_LOM20161231001STAR);
create unique index UIX_TNXRRAPT_A on TNXRRAPT_A(DATA_RIFERIMENTO, AZIENDA, NDG, SPORTELLO, CONTO, CD_TITOLO, PROGR, L3FLSOUT, PARTITION);

create table TNXRRAPT_B as select * from TNXRRAPT partition(P_LOM20161231001TGE2);
create unique index UIX_TNXRRAPT_B on TNXRRAPT_B(DATA_RIFERIMENTO, AZIENDA, NDG, SPORTELLO, CONTO, CD_TITOLO, PROGR, L3FLSOUT, PARTITION);

on a sqlplus session I execute:

select * from TNXRRAPT partition(P_0103020170630001TGET);


on a different sqlplus session I loop these two instruction


alter table TNXRRAPT exchange partition P_LOM20161231001STAR with table TNXRRAPT_A including indexes;

alter table TNXRRAPT exchange partition P_LOM20161231001TGE2 with table TNXRRAPT_B including indexes;


Many thanks,

Luca