Skip to Main Content
  • Questions

  • Unsuccessful addition of column with NOT NULL constraint ends with creation hidden columns SYS_C[...] on Oracle12c

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Andrzej.

Asked: March 17, 2017 - 3:55 pm UTC

Last updated: March 20, 2017 - 2:47 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

Hello!

Recently I've encountered a problem with adding new column to existing, not empty table. New column had a NOT NULL constraint, so ALTER TABLE command failed with ORA-01758 (that's clear).
But having looked at DBA/USER_TAB_COLS dictionary I noticed that even ALTER TABLE failed, a new column was added to the table. It had system generated name "SYS_C[....]" and datatype corresponding to the column to be added.

Since I'm not able to explain myself this behavior with creation of hidden column - I'd like to kindly ask for your support. Please find complete test case in LiveSQL attached to this question.

I tried to reproduce this on my Oracle 11g Express Edition - but it seems to work there as expected.

Thank you in advance for any help!

Best regards,
Andrzej

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/epzyij17w218bvuef6qxu595t

and Chris said...

Hmmm, interesting. I suspect this is a bug with the alter table not clearing up after itself...

If this is causing you issues, you can get rid of these hidden columns with the "drop unused columns" command:

CREATE TABLE aaa (ID NUMBER, i NUMBER);

INSERT INTO aaa VALUES (1,1);

COMMIT;

ALTER TABLE aaa ADD (a CHAR(7) NOT NULL, b NUMBER);

select column_name,data_type,data_length from user_tab_cols where table_name='AAA';

COLUMN_NAME                 DATA_TYPE  DATA_LENGTH  
ID                          NUMBER     22           
I                           NUMBER     22           
SYS_C00003_17031710:20:29$  CHAR       7            
SYS_C00004_17031710:20:29$  NUMBER     22  

alter table aaa drop unused columns;

select column_name,data_type,data_length from user_tab_cols where table_name='AAA';

COLUMN_NAME  DATA_TYPE  DATA_LENGTH  
I            NUMBER     22           
ID           NUMBER     22

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks a lot!

Andrzej Lisowski, March 20, 2017 - 11:55 am UTC

Hi Chris!

Thanks a lot for so quick answer!
I'm aware that I can use "DROP UNUSED COLUMNS" but I was curious if this is a bug or a "feature" ;-)

We'll open a SR in Metalink in order to correct this unintended I guess behavior.

Kind regards,
Andrzej
Chris Saxon
March 20, 2017 - 2:47 pm UTC

I'm not aware of anything in 12c that would need this "feature". So, yes please raise an SR!

seems to be a bug in 12c

Rajeshwaran, March 20, 2017 - 12:20 pm UTC

This looks like a bug in 12c.

Below demo is from 11g(11.2.0.4) and 12c (12.2.0.1) database.

demo@ORA11G> create table t(x int,y int);

Table created.

demo@ORA11G> insert into t values(1,1);

1 row created.

demo@ORA11G> column column_name format a10
demo@ORA11G> select column_name,hidden_column
  2  from user_tab_cols
  3  where table_name ='T';

COLUMN_NAM HID
---------- ---
X          NO
Y          NO

demo@ORA11G> alter table t add(b1 date not null,b2 number);
alter table t add(b1 date not null,b2 number)
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column


demo@ORA11G> select column_name,hidden_column
  2  from user_tab_cols
  3  where table_name ='T';

COLUMN_NAM HID
---------- ---
X          NO
Y          NO

demo@ORA11G>


no new columns in 11g, when running this against 12c got this.

demo@ORA12C> column column_name format a30
demo@ORA12C> select column_name,hidden_column
  2  from user_tab_cols
  3  where table_name ='T';

COLUMN_NAME                    HID
------------------------------ ---
X                              NO
Y                              NO
SYS_C00003_17032017:46:23$     YES
SYS_C00004_17032017:46:23$     YES

demo@ORA12C>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.