Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prince.

Asked: December 06, 2016 - 1:49 pm UTC

Last updated: December 07, 2016 - 2:23 pm UTC

Version: 12.1.0.2

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I have read lot of posts which you have discussed on the subject of adding column with some default value. Thanks a lot for such a great service you render to oracle community.

Unfortunately one of my friend informed me some bugs are associated with it for example in 11.2.0.2 , exp with direct=y would fail , query with merge keyword will error out with ORA-600..I am bit afraid..though my database is upgrade one... 12c now.

Here is my requirement..
Adding column to 11 billion rows table with value 0 as default, then in future application guys will change it to either 1 or 0 as per customer data.

First thought of adding it and updating later with value 0..but that takes huge time with most of the methods..so decided to leverage on this great feature.

1. Shall I add it like this..

alter table tablename add (column number(1,0) default 0 NOT NULL;

2. Will this be executed fast and will it impact application in any way?
3. I have HIST table of each table, whenever we change anything in base table, trigger automatically is invoked and updates the HIST table , when I add the column this way wit value 0, it will be reflected in my HIST table as well.

Regards,
Shah

and Connor said...

1/2) that will be fine - it will be done as a "fast add", ie, we simply tweak the dictionary to indicate that all existing records should also reflect the change, without actually going back and updating them.

3) No you would have to do it yourself.

Rating

  (1 rating)

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

Comments

Precise and Clear reply

Prince, December 07, 2016 - 9:19 am UTC

Hi,

Thank you very for your short and simple yet the best reply as usual..

I have another thing struck which makes my life bit tougher..

When I fired the query..It errored out...

ORA-39726: unsupported add/drop column operation on compressed tables

Table definition is like :

CREATE TABLE BKT_BASE
( SHOP_ID NUMBER(10,0),
OPP_FLAG NUMBER(1,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE FRDZ_TBS_ORD_TDE
PARTITION BY HASH (SHOP_ID)
(PARTITION PART100 SEGMENT CREATION IMMEDIATE
TABLESPACE FRDZ_TBS_ORD_TDE
ROW STORE COMPRESS ADVANCED ,......

What Can i do now?

Looks like I cant add the column with default value on it..I got to go with old way.

Hope you give some valuable suggestion on it..

Regards,
Shah
Connor McDonald
December 07, 2016 - 2:23 pm UTC

Can we have your entire DDL please

SQL> create table t ( x int, y int )
  2  partition by hash (x)
  3  partitions 2
  4  row store compress advanced;

Table created.

SQL>
SQL> insert /*+ append */ into t
  2  select rownum,rownum from dual
  3  connect by level <= 100000;

100000 rows created.

SQL>
SQL> alter table t add c1 int default 0 not null;

Table altered.

SQL> alter table t add c2 int default 0;

Table altered.


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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library