Home > oracle_11g_new_features > 11g add a column with not null to the existing table

11g add a column with not null to the existing table

Prior to Oracle database 11g, adding a new column with a default NOT NULL value were a challenge.if we have table with millions of rows and if we try to add not null column then we will get following error.

/home/oracle=>sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jun 27 17:01:56 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

SQL> create table add_notnull_column (customer_name varchar2(10),customer_city varchar2(10));

Table created.

SQL> insert into add_notnull_column values(‘UMA’,’CYPRESS’);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C
———- ———-
UMA        CYPRESS

SQL> alter table add_notnull_column add  ACTIVE_IND char(1) not null;
alter table add_notnull_column add  ACTIVE_IND char(1) not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

In this case we need to create a new table with same structure and populate the data and then rename to the original table.

Oracle database 11g offers a new quick, easy, instant solution; instead of updating ALL the rows of the table, Oracle 11g now updates the metadata only

<oracle:ora11gr21>:/home/oracle=>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jun 27 14:58:26 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Connected.
SQL> create table add_notnull_column (customer_name varchar2(10),customer_city varchar2(10));
insert into add_notnull_column values(‘UMA’,’CYPRESS’);
commit;
Table created.

SQL>
1 row created.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C
———- ———-
UMA        CYPRESS

SQL> alter table add_notnull_column add  ACTIVE_IND char(1) default ‘Y’ not null;

Table altered.

SQL> select * from add_notnull_column;

CUSTOMER_N CUSTOMER_C A
———- ———- –
UMA        CYPRESS    Y

  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: