Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, Surya.

Asked: December 02, 2002 - 9:43 am UTC

Last updated: November 19, 2012 - 8:50 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

I want to add a column in table not at the last but in between the table without dropping and recreating the table.

Say my table structure is

I want to add acolumn after Status
new column

Is it possible to do that if yes how to do that

and Tom said...

The physical order of columns is not relevant here. (i hope your code doesn't rely on it!!! very shaky)

Anyway, what you can do is this:

alter table t add new_column number;
<revoke on T_TABLE from everyone>
rename table T to T_TABLE;
create view T as
select code, date, status, new_column, actiondate
from t_table;
<grant on T to everyone that you revoked from>

Views are a great device for doing something like this -- but -- it is scary that you want columns in "some order" as that concept is foreign to a relational database.


  (21 ratings)

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


View is not required

Surya Prakash, December 03, 2002 - 4:24 am UTC

How can i alter a physical definition of database table.
Actually we create dynamic Insert statement where last first columns are reserved so only option left are to add new additional table in between (in my example after code and date and before actiondate)

Tom Kyte
December 03, 2002 - 7:19 am UTC

actually a view is required -- no idea why you say "view is not required".

what the heck is "where last first" -- last first??

Use the view, short of dropping and recreating the table from scratch that is your only option -- period.

Your method of this "dynamic insert" sounds very suspicious and fragile -- this simple exercise here is showing it's fragileness. You might rethink that logic.

But -- all else aside -- the view is required, unless you want to rebuild the table which you said "i don't want to rebuild the table".

"View is not required"

Tony Andrews, December 03, 2002 - 7:42 am UTC

> Actually we create dynamic Insert statement where last first columns are reserved

I take this to mean something like:

v_sql := 'INSERT INTO t VALUES ('
|| v_code || ',' || v_date
|| <<other values go here>>
|| ',' || v_action_date ')';

... in which case a solution would be to correct the insert statement to:

v_sql := 'INSERT INTO t( code, date'
|| <<other column names go here>>
|| ',action_date) '
|| VALUES ('
|| v_code || ',' || v_date
|| <<other values go here>>
|| ',' || v_action_date ')';

Or maybe this is bad third-party code and so cannot be fixed?

Related question

She, December 03, 2002 - 10:04 am UTC

How would I modify the length of a varchar column in a table, without dropping the table...

Tom Kyte
December 03, 2002 - 10:27 am UTC

you can make it BIGGER

alter table t modify c varchar2(BIGGER)

you cannot make it "smaller", but you can add a check constraint to make it so that only the smaller field can be put into there.

Possible=Y; advisable=N

Andrew, December 03, 2002 - 12:56 pm UTC

Well I guess it IS possible to effectively juggle column orders by adding tmp columns, populating them, and finally dropping the unwanted ones. Similar for re-sizing a column smaller (move the data out, resize, move it back again). By the time you've worked out all the DDL and DML, it would have been quicker to just rebuild though.

Being a fan of having a clean schema without "patches" to work around limitations, I'd personally prefer rebuilding the tables.

Tom Kyte
December 03, 2002 - 1:30 pm UTC

views man -- this is what views are all about.

it would take about 5 seconds with a view -- no muss, no fuss.

Never will I understand the FUD surrounding the use of views.

An easy way to add a column wherever you want !! just a logic

Kumar, December 03, 2002 - 1:24 pm UTC

If your table is not a huge then I would use this below logic, this helps if your database is 8i and latter only. See below, what is your table structure call this table as t1

t1 ( Your table existing structure)

Now you need to add column "newcolumn" after "status". See the math below to achieve this:-

alter table t1 add(newcolumn <datatype> ,actiondate_TEMP <whatever datatype>)
update t1 set actiondate_TEMP = actiondate
commit ( no need actually this commit)
alter table t1 drop(actiondate)
alter table t1 add(actiondate <whatever datatype>)
update t1 set actiondate = actiodate_TEMP
alter table t1 drop(actiodate_TEMP)

now you run the command desc t1 you can see the structure of your table as :-

new column

Note:- I mentioned these update statements to take care the data if any exists.

Tom Kyte
December 03, 2002 - 1:39 pm UTC


You win my first ever Rube Goldberg Award. Outstanding...

"The purpose of a Rube Goldberg Machine is to build the most complicated machine possible to perform a simple everyday task."

</code> <code>


Surya Prakash, December 03, 2002 - 2:02 pm UTC

Thanks Kumar. I got my answer.

Tom Kyte
December 03, 2002 - 2:22 pm UTC

you are kidding -- you won't recreate the table -- but you'll use the "curly shuffle" to totally scramble it and make it as bad as possible.

wow, amazing. amazing what hoops people will go through when the answer is staring them -- literally staring them -- in the face. totally amazing.

you'll write, rewrite, write, rewrite and totally scramble the table but you won't use a view, you won't just recreate the table as select -- both of which (in order) are infinitely superior options. Ok.....

An option is an option but no measure like superior n inferior -- choice is yours

kumar, December 03, 2002 - 2:51 pm UTC

Hi Tom,

Well I did not present this option to achieve any award nor to show how sperior my option. What I mentioned is another option ( that is why I wrote another way) and those may fit or not fit based on the criteria. For eg:- How about sometimes your create re create may not be the choices for small kind of environments like users are not allowed to do create, drop rename such previleges and the table had some foreign key constraints on other keys. However they may be allowed to alter table then this could be an easy option.

I am sure to agree with your the bullet proof option that you have presented. I am neither interested to highlight mine nor inferior the other methods of options. I Just spread or throw another way in the thread.

Oracle 8i did not had undo tablespace even then many use 8i as their production. It's an option whether we can use or not? choice is yours. I know this time perhaps you are ready to throw another award to me.

Tom Kyte
December 03, 2002 - 2:58 pm UTC

I think Andrew, who put the comment right above yours -- before you put it up here, hit the proverbial nail on the head.

possible = y
advisible = n

Reducing the varchar2 datatype column size

Pablo Rovedo, December 03, 2002 - 4:06 pm UTC

just a comment regarding modifying varchar2 columns ...

from 9i SQL Reference:

"You can reduce the size of a column s datatype as long as the change does not require data to be modified. Oracle scans existing data and returns an error if data exists that exceeds the new length limit."


Tom Kyte
December 03, 2002 - 4:28 pm UTC

cool -- stealth feature!! that one totally slipped by me, thanks so much:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x varchar2(25) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 'x' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t modify x varchar2(5);
alter table t modify x varchar2(5)
ERROR at line 1:
ORA-01441: column to be modified must be empty to decrease column length


ops$tkyte@ORA9I.WORLD> create table t ( x varchar2(25) );

Table created.

ops$tkyte@ORA9I.WORLD> insert into t values ( 'x' );

1 row created.

ops$tkyte@ORA9I.WORLD> alter table t modify x varchar2(5);

Table altered.


adding a column

Jayant Kumar, January 09, 2005 - 10:45 pm UTC

it really helped me. Thanks.

changing column's datatype

atul, May 19, 2005 - 4:27 am UTC


Could we change columns datatype in 9i,without renaming etc.. of a table with data

ie from varchar2 to number


Tom Kyte
May 19, 2005 - 8:02 am UTC

sure, IF the column is empty.

else no. it would take a

a) add new column
b) update new using old column
c) drop old column
d) rename new to old

Add column to Partition Table - Oracle 9i

kpanchan, December 19, 2006 - 2:17 pm UTC

We have partitioned table. We need add new column to this table.

Is it possible in Oracle9i Release

Solution was given by taking backup of the current table and create new table with additional column.

Copy data from old table to new table.

Reason for this approach was told, it is not possible to add new column to partition table in Oracle 9i.

could you please give share, do I have any other option?

Thanks very much for your time to answer.

Tom Kyte
December 19, 2006 - 3:50 pm UTC

ops$tkyte%ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  8  -- subpartition by hash(x)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 13  )
 14  ;

Table created.

ops$tkyte%ORA9IR2> alter table t add num number;

Table altered.

works in 8i too - always good to "try things" 

Please ignore my earlier post.

kpanchan, December 19, 2006 - 2:46 pm UTC


You already answered my question. After search your site, I got the answer.

</code> <code>

Thanks Tom.

How Alter table modify works?

Purvesh, October 12, 2010 - 6:20 am UTC

Hi Tom,

I want to understand how and Alter Table ABC Modify column Default 'N/A' works?
When we execute it, is it that the column is dropped and re-build? Or is it that it only alters the width of columns and adds the newly specified Default parameters? Do, correct my understanding. :)

In which case of Alter Table, would a column be dropped and re-build (Other than for the DROP option)?
Tom Kyte
October 12, 2010 - 8:03 am UTC

Modifying a column to have a default will only affect rows that come AFTER the alter - it doesn't touch existing data.

therefore, the table undergoes "nothing", no drop, no rebuild, nothing.

ops$tkyte%ORA10GR2> create table t ( x varchar2(30), y int );

Table created.

ops$tkyte%ORA10GR2> insert into t (y) values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t (x,y) values ( 'hello world', 2 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> alter table t modify x default 'N/A';

Table altered.

ops$tkyte%ORA10GR2> insert into t (y) values ( 3 );

1 row created.

ops$tkyte%ORA10GR2> insert into t (x,y) values ( 'hello world', 4 );

1 row created.

ops$tkyte%ORA10GR2> select * from t;

X                                       Y
------------------------------ ----------
hello world                             2
N/A                                     3
hello world                             4

How to copy a table to another schema

A reader, October 12, 2010 - 8:48 am UTC

Hi Tom,

I have two schema. Now I have to copy the structure of the table from Schema A to Schema B. The table may or may not be present in Schema B. There can be situations like:

1. Table is present in Schema B but not in Schema A
2. Table is present in both schemas but differ in column structure.
3. Same table with same columns is present in both schema.

I have to copy these without taking the database down. Can we do it through TOAD? I saw there is a option to CREATE TABLE in ANOTHER SCHEMA but I am not sure about it.

It would be great if you suggest me the ways to copy the tables with/without data in live database..
Tom Kyte
October 12, 2010 - 9:19 am UTC

ask the people that make toad questions about toad please. I do not use toad myself.

I don't know how to copy a table from A to B if (1) is true.

I don't know what it means to copy a table form A to B is (2) is true - what would you expect to have happen???

(3) is trivial, log into B and issue "delete from table; insert into table select * from a.table;"


Abhisek, October 12, 2010 - 12:38 pm UTC

Hi Tom,

I am sorry if I was unclear. I want to simply copy one schema to another like we do with exp/imp utility.

The three were conditions about existence of objects. I am not concerned for data. I need Objects in Schema B are same as Schema B.

I could use COPY command from sqlplus to copy data.But I am not sure what happens with this COPY command if you have tables in both schema but different strucure,.

To make it simple, lets consider a prod alike schema needs to be created.
Tom Kyte
October 12, 2010 - 12:57 pm UTC

how can you call

"2. Table is present in both schemas but differ in column structure."

a simple copy ??? the structures are different - that is hugely complex all of a sudden.

... I need Objects in Schema B are same as Schema B. ...

er? not sure what you mean.

... But I am not sure what
happens with this COPY command if you have tables in both schema but different
strucure,. ...

laughing out loud - I'm not sure what should happen in your mind when this occurs. How do you do that? What is the process.

... To make it simple, lets consider a prod alike schema needs to be created.

a prod alike? or do you mean a "prod like schema"

I would say that data pump or export import would be the path of least resistance - either one can remap schema names - but you would start with an EMPTY schema.

This is not very clear at all - especially when you throw in #2.

If you just want a copy of a schema - table by table, column by column - exp/imp is the way to go using fromuser touser, or datapump - depending on your version.

ABhisek, October 12, 2010 - 1:41 pm UTC

Glad I was a reason for your laugh for one sec.. ;p

OK let me make my stand clear. I want to copy all the objects from Schema B to Schema A. I do not have empty schema. There are objects in Schema A but still not 100 % similar to schema B.

May be entire table is missing or may be some columns are missing. So, I have to make these two schemas similar.. May not be in terms of data but object definitions.

Now I hope I am clear. Thanks for the help.
Tom Kyte
October 12, 2010 - 2:02 pm UTC

you want to have a set of creates and alters created for you to make schema "A" look like schema "B"

(you do not want to copy all of the objects by the way, that would imply you wanted to COPY the objects - not synchronize the physical schemas of two things)

I hate this - you would, in a proper environment, have the "patch scripts" you would pull out of source code control that take your schema from version 1 to version 2. Having to "diff" two schemas to make them the same means "someone missed the boat big time".

sql developer has such a capability:

Schema Copy

ABhisek, October 12, 2010 - 5:03 pm UTC

SO finally we are on same page.. I have all the source code in a subversion but I am not sure if we have to disable all the contraints before I create the table to maintain the primary or foriegn key relation..

I am sorry but this is the first time I am trying this type of operation.. So I need some understanding before I do it.

Please suggest the best way considering I have all codes in Subversion.

Thanks a lot for the help.
Tom Kyte
October 12, 2010 - 5:33 pm UTC

Tell me - how did production and this environment get to "differ" - where are THOSE scripts - those scripts that changed the one environment should have been

a) checked in
b) tested
c) available for you to now check out and run yourself.

Same query I have

A reader, October 12, 2010 - 5:42 pm UTC

To add to the previous question, I have seen a Server 1 which was supposed to be for initial development work and then saved to subversion. But people started in Server 2 and now after 1+ years I see a huge mismatch.

So now how can we make a schema to be compliant with what is present in repository. Please suggest.
Tom Kyte
October 12, 2010 - 6:40 pm UTC

see my answer above - I provided a name (sql developer) and a link (to feature of sql developer).

Alter table add column

Abhishek Das, November 07, 2012 - 12:12 am UTC

Why does oracle doesn't provide this feature of adding column in between two columns?
We have this feature in other database systems with commands like "alter table t1 add new_column number after/before a1".
I have read your posts and know we can achieve this using view, but out of curiosity I just wanted to know what is the reason that Oracle doesn't provide this functionality.
Also Can we still use this option of creating view if we have triggers on the table?
Tom Kyte
November 07, 2012 - 7:44 am UTC

triggers have no impact on views

we just don't have it - that is all, no reason. to me it isn't a big deal because select * is something you only have in demo's anyway, the order of columns in the table doesn't even guarantee what select * will resolve to, it is just an empirical observation that it seems to be the order of columns in the create....

there are cases

Sokrates, November 07, 2012 - 8:07 am UTC

... where the ordering of columns could matter
for example
Tom Kyte
November 07, 2012 - 11:30 am UTC

ah, but that is apples and banana's

the before/after 'trick' is purely a metadata thing, it doesn't really put the data before/after - just makes that column "appear" there.

you have to rebuild the entire table to put a column "in the middle" of a table - and we can do that, dbms_redefinition will do it, create table as select will do that.

there is a big difference between the syntactic sugar that puts column X between A and B in metadata

and actually having column X between A and B physically in the database block.

alter table add col

Onkar, November 14, 2012 - 10:46 pm UTC


One of the developer ask me this:
I wanted to add a column in an existing table using select stmt. What I mean is:
SQL:SCOTT@tp11g>create table test as select ename as name ,job from emp;

Table created.

Now I want to add sal column with data from emp to test using select stmt. I doubt we can do it but not sure. is it possible?

Tom Kyte
November 19, 2012 - 8:50 am UTC

it is not possible, no