Solved

alter table

Posted on 2004-10-15
5
3,762 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Not able to add the URL for the   access control list - oracle plsql 10 164
Export table into csv file in oracle 10 274
Oracle sql query 7 76
Oracle - SQL Parse String 5 47
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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 restore a database from backup after a simulated disk failure using RMAN.

808 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