Mohamed Houri’s Oracle Notes

August 7, 2013

Indexed virtual column and DML error logging : ORA-03113: end-of-file on communication channel

Filed under: Oracle — hourim @ 2:57 pm

This is a brief note to show you that when combining virtual column and DML error logging you might be in trouble as I have been.  First the model

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
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


create table t1(n1 number, dat1 timestamp(6));

alter table t1 add virt_n1 generated always as (case when dat1 is null then n1 else null end) virtual;

create index ind_virt_n1 on t1(virt_n1);

create table t2(n1 number, n2 number);

insert into t1 (N1,DAT1) values (1067597,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (1067597,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (1067597,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (1067597,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (1067597,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (36869,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (36869,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (170012,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (170012,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (170012,to_timestamp('05-AUG-13 10.44.09.456703000 AM','DD-MON-RR HH.MI.SS.FF AM'));
insert into t1 (N1,DAT1) values (170012,null);

insert into t2 select distinct n1, n1+1 from t1;

commit;

exec dbms_errlog.create_error_log (dml_table_name => 't1');

I have created two tables, t1 and t2.  I enriched t1 with a virtual column (virt_n1) and a non unique index (ind_virt_n1) on this virtual column. And finally, I create a DML error logging table (err$_t1) in order to log rejected records when inserting into t1;

And now the problem

insert into t1
    (n1
    ,dat1)
select
   t2.n1
   ,systimestamp
from t2
log errors into ERR$_t1 reject limit unlimited;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

It took me a couple of minutes to figure out that this error is due the DML logging error because when I get rid of this error logging the insert works perfectly

insert into t1
(n1
,dat1)
select  t2.n1
,systimestamp
from t2;

3 rows created.

But wait; I was not going to let down my DML error just because of this end-of-file on communication channel?  I was curious to know the reason that impeaches my insert to work when combined with the DML error logging. The way I did approached this issue is to ask myself what is the difference between the actual situation and the old and many situations where I did smoothly used the DML error logging. You might have already guessed the answer because it is in the title of this blog article: virtual column. So, I immediately described the err$_t1 which shows the presence of the virtual column


SQL> desc err$_t1
Name                            Null?    Type
------------------------------- -------- ---------------
1      ORA_ERR_NUMBER$                          NUMBER
2      ORA_ERR_MESG$                            VARCHAR2(2000)
3      ORA_ERR_ROWID$                           ROWID
4      ORA_ERR_OPTYP$                           VARCHAR2(2)
5      ORA_ERR_TAG$                             VARCHAR2(2000)
6      N1                                       VARCHAR2(4000)
7      DAT1                                     VARCHAR2(4000)
8      VIRT_N1                                  VARCHAR2(4000) -- virtual column

By simply dropping the virtual column from the err$_t1 the insert works perfectly  in the presence of DML error logging table as shown below:

alter table err$_t1 drop column virt_n1;
Table altered.

insert into t1
(n1
,dat1)
select  t2.n1
,systimestamp
from t2
log errors into ERR$_t1 reject limit unlimited;

3 rows created.

I was still curious to know why the presence of a virtual column in the error table impeaches things to work correctly. There might be another reason. This is why I started as an electrician who is having an electrical problem in his installation but doesn’t know exactly which is the culprit electrical devise is.  I modeled my real life tables and started dropping object by object (foreign key, unique key, index) until I found the culprit object:  ind_virt_n1 the index on the virtual column.

If you re-create the model presented above and, this time, drop the index ind_virt_n1 and let the virtual column in the err$_t1 table the insert will this time work perfectly as shown below:

drop index ind_virt_n1;
Index dropped.

insert into t1
    (n1
    ,dat1)
select
    t2.n1
   ,systimestamp
from t2
log errors into ERR$_t1 reject limit unlimited;

3 rows created.

The bottom line of this article is to show that mixing indexed virtual column and DML error logging might not work without error. In my case I opted for dropping the virtual column from the err$_t1 instead of dropping the virtual index because of the performance gain this index brings to my application.

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Oracle Database 11g

Oracle Database

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Oracle SQL Tuning Tools and Tips

SQLTXPLAIN (SQLT), TRCANLZR (TRCA), SQL Health-Check (SQLHC) and SQL Tuning Topics

Oracle Scratchpad

Just another Oracle weblog

Tanel Poder's blog: Responsible data management

Linux, Oracle, Exadata and Hadoop.

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

Follow

Get every new post delivered to your Inbox.

Join 64 other followers

%d bloggers like this: