wuyue15
asked on
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?
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?
ASKER
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?
Are there any other ways?
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
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
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.
(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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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);