alter table

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?
wuyue15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bigfam5Commented:
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
wuyue15Author Commented:
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
annamalai77Commented:
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
cjjcliffordCommented:
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
cjjcliffordCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.