Oracle Scratchpad

December 30, 2017


Filed under: Infrastructure,Oracle — Jonathan Lewis @ 12:08 pm GMT Dec 30,2017

Here’s an odd little quirk that appeared when I was playing around with default values just recently. I think it’s one I’ve seen before, I may even have written about it many years ago but I can’t find any reference to it at present. Let’s start with a script that I’ll run on (the effect does appear on earlier versions):

rem     Script:         nvarchar2_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Dec 2017

create table t1 (
        column1  varchar2(10),
        column2  nvarchar2(10)

create table t2 (
        column1  varchar2(10)

alter table t2 add column2 nvarchar2(10);

create table t3 (
        column1  varchar2(10),
        column2  nvarchar2(10) default 'xxxxxxxx'

create table t4 (
        column1  varchar2(10)

alter table t4 add column2 nvarchar2(10) default 'xxxxxxxx';

insert into t1(column1) values('a');
insert into t2(column1) values('a');
insert into t3(column1) values('a');
insert into t4(column1) values('a');

All I’ve done it create 4 tables which, when described, all look the same:

 Name                    Null?    Type
 ----------------------- -------- ----------------
 COLUMN1                          VARCHAR2(10)
 COLUMN2                          NVARCHAR2(10)

There is a significant different between the first two and the last two, of course, thanks to the specification of a default value which means that the inserts will produce two possible results: the first two tables will have nulls in column2; the last two will have the nvarchar2 equivalent of ‘xxxxxxxx’ which, in my instance, will be a string of 16 bytes: “0,78,0,78,0,78,0,78,0,78,0,78,0,78,0,78”.

Surprisingly, though, there is a dramatic oddity between t3 and t4 which shows up when I query user_tab_cols:

        table_name, column_id, column_name,  segment_column_id, data_default
from    user_tab_cols
where   table_name like 'T_'
order by
        table_name, column_id

-------------------- ---------- -------------------- ----------------- --------------------
T1                            1 COLUMN1                              1
                              2 COLUMN2                              2

T2                            1 COLUMN1                              1
                              2 COLUMN2                              2

T3                            1 COLUMN1                              1
                              2 COLUMN2                              2 'xxxxxxxx'

T4                            1 COLUMN1                              1
                              2 COLUMN2                              3 'xxxxxxxx'
                                SYS_NC00002$                         2

Table t4 has acquired two columns – a hidden column (which physically exists as the second column in the stored data and is declared as raw(126)) and the column which I had specified. You’ll note that the test shows two differences that may be significant: comparing t3/t4 we see that adding, rather than initially defining, the nvarchar2() column introduces the extra column; comparing t2/t4 we see that adding a varchar2() rather than an nvarchar2() doesn’t produce the same effect. Tentative assumption, therefore, is that there is something special about adding nvarchar2() columns. [Update: wrong, see comment 2 and the link it supplies]

Casting my mind back to various customers who have gone through multiple upgrades of 3rd party applications that invariably seem to add columns to tables, I wondered whether this extra column appeared every time you added an nvarchar2(). I’d not noticed anything in that past that suggested this might be the case, but it’s obviously worth checking: and in my simple tests it looked as if Oracle created just one extra column and used it to capture a value that seemed to be determined by the number and location of columns that had been added.

It’s a curiosity, and leaves room for further investigation – so if anyone has links to related articles please feel free to add them in the comments.


The effect is due to an enhancement in 12c relating to “add column default”. In 11g Oracle introduced an enhancement that allowed the addition of a “not null” default column by storing the value in the data dictionary rather than having to add it immediately to every row. The mechanism could not be used to add a default value for a column that allowed nulls. In 12c Oracle added a hidden colum that used a bit flag to resolve this issue. (See article  linked in the comments, and this discussion on the OTN database forum.)


  1. I have just executed your script on EE Win x64 and I do not observe these additional ‘hidden’ columns. I am somewhat relieved given I have client production scripts to alert developers that DB production code and table definitions are out of sync with that under source control.

    Comment by Michael D O'Shea (@MichaelDOShea) — December 30, 2017 @ 12:43 pm GMT Dec 30,2017 | Reply

    • Michael,

      Thanks for the feedback. Possibly it doesn’t appear until 12c – I’ve reproduced the behaviour in

      That’s a good point about reports of differences between development and production systems. It does depend on which views and columns you use for comparison, of course: user_tab_cols shows the hidden columns, of course, while user_tab_columns doesn’t; and in this case the column_id matches even when the segment_column_id tells you something different.

      Comment by Jonathan Lewis — December 30, 2017 @ 1:09 pm GMT Dec 30,2017 | Reply

  2. The difference between T2 and T4 is the presence of the default value, not varchar2 vs nvarchar2. This is about adding a new column with a default value, and this is not specific to nvarchar2, the outcome is the same with varchar2, number, date.
    Hidden Column “SYS_NC000XX$” Being Added When A New Column Is Added To The Table. (Doc ID 2277937.1)

    This behaviour can be disabled with:

    alter session set “_add_col_optim_enabled”=false ;

    [Update May 2019 – JPL]
    The referenced article has disappeared, but is still available in Chinese translation at
    I found it through a Google search for “2331068” and then selected Google’s offer to “translate this page”

    The MoS Doc ID is still relevant.

    Comment by Balazs Papp — December 30, 2017 @ 5:04 pm GMT Dec 30,2017 | Reply

    • Balazs,

      Thanks for the link.

      A case of stopping too soon before trying to find a flaw with my first hypothesis.

      Comment by Jonathan Lewis — December 30, 2017 @ 5:29 pm GMT Dec 30,2017 | Reply

    • Hi Jonathan,

      This DDL optimization stuff caused quite a significant headache to our alter table exchange mexhanism, which we had been using for a long time for loading/unloading tables. The first was in 11.1 with the default not null optimization and the second came with 12.1, where oracle optimized even adding column with default value.
      It is quite tricky to create table for exhange, when the ddl optmization kicks in. For 11.1 feature is is sufficient to force table to be created with proper hakan factor by using event 14529 inside session before CTAS is issues.
      I’m not sure if it is documented for 12.1 feature now, but to create table with proper hidden columns is possible by setting event 14529, level 512. We were forced to file a SR for this on MOS. It took quite a long untill we received this workarroud.

      Pavol Babel
      OCM 10g/11g

      Comment by Pavol Babel — December 31, 2017 @ 12:59 pm GMT Dec 31,2017 | Reply

      • Pavel,

        Thanks for the comments – that’s useful information for others to see.
        12.2 has “create table SIMPLE_TABLE for exchange with table PT_TABLE”, so that’s probably going to avoid the issue on the next upgrade.

        Comment by Jonathan Lewis — December 31, 2017 @ 5:46 pm GMT Dec 31,2017 | Reply

  3. How can we drop this hidden column? because it causes error in exchange partition process

    Comment by Anonymous — October 26, 2019 @ 2:29 pm BST Oct 26,2019 | Reply

    • Anonymous,

      I don’t think you can do this easily. I just had a quick look to check the internal treatment.

      As you can see above when you add the column with default Oracle phusically adds two columns, but the order is “wrong” – the first column it adds is a hidden column to carry the default value and the second column is the one you want. So to drop the hidden column to be able to create a table that looked the same you would have to do a complete physical rewrite of the table at some point.

      Fortunately in 12.2 you can now do:

      create table simple_tab for exchange with table pt_tab;

      This will create an exact data dictionary match of the table definition for you, so that you can exchange simple_tab with a partition of pt_tab. So if you can get to that version you will be okay.

      If you’re on 12.1 I think the only thing you can do is to make sure you know the history of the partitioned table and emulate it the first time you create the simple table – so create table, add column with default.

      Comment by Jonathan Lewis — October 31, 2019 @ 12:19 pm GMT Oct 31,2019 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: