Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

alter table

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
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.
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

618 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