Solved

alter table

Posted on 2004-10-15
5
3,766 Views
Last Modified: 2007-12-19
I want to change the datatype of a column in one table but when i used

ALTER TABLE NEWALARM MODIFY (STARTTIME TIMESTAMP)

But I got an error message to say:
ORA-01439 column to be modified must be empty to change datatype.

So are there any method for me can change the datatype of a non-empty column?
0
Comment
Question by:wuyue15
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Expert Comment

by:Bigfam5
ID: 12319105
Sure, there are several ways

create table NEWALARM_old as (select * from NEWALARM);

delete from NEWALARM ;
or
truncate table NEWALARM;

ALTER TABLE NEWALARM MODIFY (STARTTIME TIMESTAMP);

insert into NEWALARM (select * from NEWALARM_old);


0
 

Author Comment

by:wuyue15
ID: 12319189
I understood you solution. but it is a big table with millons rows. I cannot create a new one and delete it within a short time.

Are there any other ways?
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 12319210
hi there

the message clearly says that u cannot modify the column when u have data in the column. u can only increase the width of column even when data is there in that column,.

to modify a column first create a temp table
then truncate the master table
modify the column
do an insert from temp table to master table.

create table temp_students as select * from students;
truncate table students;
alter table students modify(starttime timestamp);
insert into students(column name1, column_name2, timestamp)
(select c1, c2, to_Date(st_dt, 'dd/mm/ytyyy hh:mi:ss) from temp_students;

the reason for formatting the column is because, i assume that the timestamp column which u modified now was earlier a varchar2 column.

regards
annamalai

regards
annamalai
0
 
LVL 11

Expert Comment

by:cjjclifford
ID: 12319483
easier than truncating and re-populating the existing table is to create a new table with the new desired columns (i.e. timestamp as a date!) -
(make sure you create it in the correct tablespace - i.e. the tablespace you want the data to be in at the end....)

CREATE TABLE new_data_table( .... timestamp date, ....) TABLESPACE correct_tablespace;

then populate it, including the column type change

INSERT INTO new_data_table SELECT ... FROM students;
COMMIT;

then drop the existing table (taking care, to remove cascade delete constraints on other tables.... etc)
DROP TABLE students;

then rename the new table...
RENAME new_data_table students;
(this might be ALTER TABLE RENAME...)

Then recreate all the indexes, constraints, foreign keys etc.
0
 
LVL 11

Accepted Solution

by:
cjjclifford earned 50 total points
ID: 12319516
of course, if this seems over the top, a slow, but easier on the typist!, way of doing it would be to add a new column on the table to contain the DATE

ALTER TABLE student add timestamp DATE;

then UPDATE the table...

UPDATE student SET timestamp = to_Date(st_dt, 'dd/mm/ytyyy hh:mi:ss);

then drop the old column...

ALTER TABLE REMOVE column st_dt;

make sure you have plenty of undo/rollback when doing it this way...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question