?
Solved

alter table

Posted on 2004-10-15
5
Medium Priority
?
3,769 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 150 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 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