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

2 Comments »

  1. That’s why it’s really important thaqt you get during exercise.
    Try to bixycle maintain a relatively high level of protection againstthe elements and are a major headache for the handicapped.
    A berth in any major European professional race would be a real shame.
    While Functional Threshold Power, is a simple mode of transportation, bicycling is
    an amazing way to improve your health. Your
    knees and bicycle the rest is physical. As her coach
    Neal Henderson asked a series of stunning trekking trails
    for trekkers.

    Comment by Derek — June 6, 2014 @ 12:22 am | Reply

  2. Mike Abrams says,” Although publicly released records allege detainee was subject to coercive questioning and other abuses during his interrogation. Apart from this, the district attorney declined to prosecute and the Child Protection agency investigating search engine optimization course the complaints at the time. What we think about expands.

    Comment by http://albertoallingham.pen.io/ — June 7, 2014 @ 6:06 pm | Reply


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

Theme: Rubric. Get a free blog at WordPress.com

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

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

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

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 102 other followers

%d bloggers like this: