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:

• The DBA Guide to the SQL Server Database Engine .NET CLR (whitepaper)
• MSSQL 2000: Service Pack 4
• MSSQL 2005: BACKUP LOG changes
• MSSQL: The value of Default Constraint
• MSSQL 2000: Row Count Using sysindexes System Table
• MCP Hall of Fame
• MSSQL: Paul McBride on ISNULL vs. COALESCE
• MSSQL: Quest Software to compete Red-Gate
• SQL Server 2005 Product Line published
• Advanced MSSQL 2005: Partitioning, memory management, etc

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.

As of Oracle9i, the following statement will rename a column:
ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>;

Jonathan: I want to change table column data type, not to rename a column

how to change the column data type of one of the column of existing table?

Akhilesh: it is described step-by-step in the post

hi

Good example!

ALTER TABLE <table_name> MODIFY <column_name> <new_data_type>

Josh: It does not work in Oracle.

alter table mifamily modify(nombre varchar2(43));
NOTE: To modify the Datatype the column must be all null

mt30march: LOL, they are not null...

- Change column_name to varchar(20)
ALTER TABLE table_name MODIFY (column_name VARCHAR(20));

lol

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?

Christy: when there is no data in the table it should work, how do you check the data length.

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...

oops, I meant
select data_type, data_length from user_tab_columns
where column_name = 'COLUMN_NAME'

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.

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.

Christy: ok, thanks for the info :)

alter table
table_name
modify
(
column_name varchar2(30)
);

deer777: there is data in the table, sorry

Thanks, very useful.

PS good logo, I love Cowboy Bebop

Q

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.

David: thanks for pointing that out. May be I misunderstood reply by Jonathan.

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.

but, what can I do if original column is primary key, and there are tables with references at that primary key...??

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

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));

Very good article thank you...

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