Anatoly Lubarsky Logo
programming, design, integration, games, music

How to change column datatype in Oracle table (ALTER TABLE).

Suppose we need to change column data type in Oracle database table. Suppose also that the table has some data. Let's say 1000000 rows. I want to change datatype from NUMBER to VARCHAR2. Lets see. Oracle doesn't allow me to modify column this way when there is data in the table. I can not also rename the column.


Solution:


  1. ALTER TABLE: add new column (temp) with desired datatype.
  2. UPDATE: (copy data to the new column using piecemeal batch size)
  3. ALTER TABLE: drop the original column
  4. ALTER TABLE: add new column with the name of the original and the desired datatype
  5. UPDATE: (copy data to the new column from the temp column using piecemeal batch size)
  6. ALTER TABLE: drop temp column


Now you say - which one is more powerful: MSSQL or ORACLE ? Although the same approach is considered as best practices for MSSQL also, but the fact is that MSSQL is still more flexible since you can rename the new column after dropping the original.


Related Posts:

Tuesday, August 30, 2005 1:28 AM

Comments

# re: Oracle: ALTER TABLE (MODIFY column)
PostGreSQL is better than both of them put together. Other than in a few cases though Oracle is a much better RDBMS than MSSQL. Especially if you are dealing with huge amounts of data like storing mechanical drawing files or engineering schematics as whole files in the Database. MSSQL absolutely sucks for doing that. But MSSQL is a lot easier to use than Oracle is from a Dba point of view so I guess it depends on what you are looking for. For many things companies do ease of use for the Dba affects how good the RDBMS is for them so MSSQL is the way to go. Other companies want reliability and massive datasets with terrabytes of data which absolutely cannot go down if they want to maintain their five nines. Which is why most telecomm's use Oracle running on Sun Solaris boxes. Harder on the Dba's to work with but all the 911 calls go through and nobody dies because of a dropped call. It really depends on what you are doing as to which you would want to go with. Personally for what I do I'll take Oracle over MSSQL and PostGreSQL over both of them.

8/30/2005 2:00 AM by Andy

# Oracle: development tool needed

10/7/2005 10:01 PM by Anatoly Lubarsky: Weblog

# re: Oracle: ALTER TABLE (MODIFY column)
As of Oracle9i, the following statement will rename a column:
ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>;

10/19/2005 5:26 AM by Jonathan

# re: Oracle: ALTER TABLE (MODIFY column)
Jonathan: I want to change table column data type, not to rename a column

10/20/2005 1:37 AM by Anatoly Lubarsky

# re: Oracle: ALTER TABLE (MODIFY column)
how to change the column data type of one of the column of existing table?

1/20/2006 7:16 AM by Akhilesh Gupta

# re: Oracle: ALTER TABLE (MODIFY column)
Akhilesh: it is described step-by-step in the post

1/20/2006 1:10 PM by Anatoly Lubarsky

# re: Oracle: ALTER TABLE (MODIFY column)
hi

1/24/2006 12:17 PM by srinu

# re: How to change column datatype in Oracle table (ALTER TABLE).
Good example!

5/22/2006 8:21 PM by Doesn't matter

# re: How to change column datatype in Oracle table (ALTER TABLE).
ALTER TABLE <table_name> MODIFY <column_name> <new_data_type>

5/26/2006 10:02 PM by Josh

# re: How to change column datatype in Oracle table (ALTER TABLE).
Josh: It does not work in Oracle.

5/27/2006 2:19 AM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
alter table mifamily modify(nombre varchar2(43));
NOTE: To modify the Datatype the column must be all null

6/1/2006 10:02 AM by mt30march

# re: How to change column datatype in Oracle table (ALTER TABLE).
mt30march: LOL, they are not null...

6/1/2006 10:21 AM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
- Change column_name to varchar(20)
ALTER TABLE table_name MODIFY (column_name VARCHAR(20));

6/27/2006 2:04 AM by Juarez

# re: How to change column datatype in Oracle table (ALTER TABLE).
lol

6/27/2006 2:46 AM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
What about trying to modify a column in Oracle that definitely has no data in the table, and it won't work. For instance, I've been trying to change the data_length of the column, and I don't get an error message, I get 'table altered' and yet even after a commit it shows up as the old data_length. Tried dropping the column, committing (queried to make sure it was gone), created a new column with the correct data_type and data_length, and committed, then it STILL used a different data_length. How is this possible?

7/19/2006 9:04 PM by Christy

# re: How to change column datatype in Oracle table (ALTER TABLE).
Christy: when there is no data in the table it should work, how do you check the data length.

7/19/2006 9:12 PM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
select data_type, data_length from user_tab_columns
where column_name = 'TABLE_NAME'

I'm also using SQL_STATION and actually looking up the table...

7/19/2006 9:31 PM by Christy

# re: How to change column datatype in Oracle table (ALTER TABLE).
oops, I meant
select data_type, data_length from user_tab_columns
where column_name = 'COLUMN_NAME'

7/19/2006 9:35 PM by Christy

# re: How to change column datatype in Oracle table (ALTER TABLE).
As it turns out, the default for Oracle is 22 for the datatype NUMBER.
So my changes were effective, but when I pull up the user_tab_columns query it shows the Oracle default for NUMBER instead of actual data_length.
I'm not sure why this is, but when I checked the actual code in SQL_STATION to create the table it definitely listed the data_type and data_length for the column I was concerned with correctly.

7/19/2006 11:30 PM by Christy

# re: How to change column datatype in Oracle table (ALTER TABLE).
OK, also, my query should have asked for data_precision, not data_length. Data_length within user_tab_columns asks for the total number of characters allowed by default for the data_type. Precision asks for exactly what you set the data_type at, and scale would of course be any digits after the decimal.

7/19/2006 11:37 PM by Christy

# re: How to change column datatype in Oracle table (ALTER TABLE).
Christy: ok, thanks for the info :)

7/20/2006 2:59 AM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
alter table
table_name
modify
(
column_name varchar2(30)
);

7/25/2006 7:30 PM by deer777

# re: How to change column datatype in Oracle table (ALTER TABLE).
deer777: there is data in the table, sorry

7/25/2006 7:42 PM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
Thanks, very useful.

PS good logo, I love Cowboy Bebop

Q

3/13/2007 11:28 AM by Queez

# re: How to change column datatype in Oracle table (ALTER TABLE).
Anatoly: regarding your reply to Jonathan, above, he was responding to your statement at the end of your post that MSSQL is more flexible because it allows you to rename the new column after dropping the original. He was pointing out that Oracle allows you to do this, too.

3/19/2007 10:11 PM by David Conrad

# re: How to change column datatype in Oracle table (ALTER TABLE).
David: thanks for pointing that out. May be I misunderstood reply by Jonathan.

3/19/2007 10:18 PM by Anatoly Lubarsky

# re: How to change column datatype in Oracle table (ALTER TABLE).
Jonathan method will change Column_ID (order)
With text column, there re no proble when you extend column.
But with number col: You can create temp to hold old data and set null old column then modify old column and last copy data back.

7/17/2007 9:04 AM by ngonhan2k5

# re: How to change column datatype in Oracle table (ALTER TABLE).
but, what can I do if original column is primary key, and there are tables with references at that primary key...??

2/11/2008 3:28 PM by Daniel

# re: How to change column datatype in Oracle table (ALTER TABLE).
i want to update a fields datatype to clob,

i am using the following code to do this,

ALTER TABLE EPR_EMPLOYEE MODIFY (RESUME_NAME(CLOB));

it says invalid datatype, can any one please help me

2/26/2008 2:52 PM by Saurabh

# re: How to change column datatype in Oracle table (ALTER TABLE).
Altering the column type
-------------------------

to alter a column type, two conditions are their.
1. either table should not contain any data.
2. either that column should not contain any data.

alter table mytable ( modify id varchar2(10));

3/9/2008 4:33 PM by sandeep

# re: How to change column datatype in Oracle table (ALTER TABLE).
Very good article thank you...

1/1/2009 1:24 PM by nakliyat

# SQL for oracle and MySQL &laquo; joysana (Web Solution)
SQL for oracle and MySQL &laquo; joysana (Web Solution)

11/3/2011 1:39 AM by Pingback/TrackBack

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January 2018
(01) June 2013
(03) March 2013
(02) February 2013
(01) July 2012
(01) April 2012
(01) September 2011
(01) August 2011
(03) May 2011
(01) December 2010
(01) November 2010
(01) October 2010
(01) June 2010
(01) May 2010
(02) March 2010
(01) January 2010
(02) December 2009
(03) September 2009
(03) August 2009
(09) July 2009
(04) June 2009
(03) May 2009
(02) April 2009
(03) March 2009
(02) February 2009
(02) January 2009
(04) December 2008
(04) November 2008
(05) October 2008
(04) September 2008
(05) August 2008
(04) July 2008
(05) June 2008
(07) May 2008
(04) April 2008
(03) March 2008
(02) February 2008
(03) January 2008
(03) December 2007
(05) November 2007
(04) October 2007
(05) September 2007
(12) August 2007
(11) July 2007
(14) June 2007
(13) May 2007
(13) April 2007
(10) March 2007
(11) February 2007
(14) January 2007
(14) December 2006
(12) November 2006
(08) October 2006
(09) September 2006
(06) August 2006
(08) July 2006
(10) June 2006
(09) May 2006
(22) April 2006
(25) March 2006
(12) February 2006
(14) January 2006
(19) December 2005
(17) November 2005
(16) October 2005
(16) September 2005
(12) August 2005
(14) July 2005
(09) June 2005
(12) May 2005
(12) April 2005
(20) March 2005
(11) February 2005
(12) January 2005
(18) December 2004
(13) November 2004
(12) October 2004
(14) September 2004
(09) August 2004
(23) July 2004
(19) June 2004
(29) May 2004
(19) April 2004
(16) March 2004
(09) February 2004
(06) January 2004
(02) December 2003
(01) November 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs