How about this?
Dan Kefford, December 15, 2004 - 4:08 pm UTC
Tom.... couldn't you use this strategy?
SQL> create table blah_dk
2 (num_col number)
3 /
Table created.
SQL> insert into blah_dk
2 select rownum from all_objects
3 where rownum < 11
4 /
10 rows created.
SQL> commit
2 /
Commit complete.
SQL> alter table blah_dk add
2 (varchar_col varchar2(3))
3 /
Table altered.
SQL> update blah_dk
2 set varchar_col = num_col
3 /
10 rows updated.
SQL> alter table blah_dk drop column num_col
2 /
Table altered.
Of course, I didn't take into consideration whether num_col had any indexes, constraints, etc. Wouldn't this be any easier or less risky than dropping and recreating the whole table?
December 15, 2004 - 6:28 pm UTC
row migration
foreign keys
constraints
indexes
there would be even more row migration going on (have the number and the varchar2 at the same time)
same fkey issues (they mentioned key field) and so on.
Exp/Imp would be "unsafe" (when you take the data out of the database - you are at risk)
we are not dropping and recreating -- we are recreating and then dropping. You copy the data over -- even if you drop the old data, you still have it.
In 9i, they could use an online redefine -- but they are in 8.x....
Changing datatype without changing name
Bob B, December 15, 2004 - 5:27 pm UTC
Dan, I believe you forgot one piece of the whole puzzle: changing the datatype leaves the name the same.
If all access to the table was done through a view, say,
CREATE OR REPLACE VIEW blah_dk_view as
SELECT num_col
FROM blah_dk
then
CREATE OR REPLACE VIEW blah_dk_view as
SELECT varchar_col num_col
FROM blah_dk
Can probably handle many of the naming issues. If that is not the case, then the column migration has to occur twice once to move the column data to a new name and data type and a second time to move it back to the original name. If you decide you want to be able to recover from this change, then you'll probably end up using CTAS to backup the data anyway, so you're probably better off sticking to simple and safe.
Additional
Bob B, December 15, 2004 - 5:35 pm UTC
I meant to put this in my previous post, apologies. The CTAS method can be made "safer" by renaming the old table and keeping it as a backup for however long you think your paranoia lasts: i.e. (Note: I added the 'c' in front of the n field to show that n was now a varchar2)
CREATE TABLE t (
n NUMBER
)
Table created
INSERT INTO t
SELECT ROWNUM
FROM ALL_USERS
WHERE ROWNUM <= 10
10 rows inserted
SELECT *
FROM t
N
--
1
2
3
4
5
6
7
8
9
10
10 rows selected
CREATE TABLE t_new AS
SELECT 'c' || TO_CHAR( n ) n
FROM t
Table created
RENAME t TO t_old
Table renamed
RENAME t_new TO t
Table renamed
SELECT *
FROM t
N
-----------------------------------------
c1
c2
c3
c4
c5
c6
c7
c8
c9
c10
10 rows selected
Response to Bob B on December 15, 2004
Dan Kefford, December 15, 2004 - 5:40 pm UTC
DOH! Knew I would have missed at least _one_ detail. ;P
Well... at this point, I'm probably barely defending my argument, but one could still do this:
SQL> alter table blah_dk rename column varchar_col to num_col
2 /
Table altered.
Renaming column in place
Bob B, December 15, 2004 - 9:07 pm UTC
If we were going to use the add a column method, I guess the following might work:
Add new number column (yes, number)
update new column with old columns value
disable constraints/remove indexes (if needed)
change the old column from number to varchar2
update the old column with the new_column values
rebuild indexes/enable constraints
drop new column
still seems like an awful amount of work and a lot unsafer than the CTAS, rename, rename method
Need some help
Terry, June 13, 2005 - 8:10 am UTC
Hi Tom,
I tried to change a column's datatype of a table with 13G of data, and faced a lot of problems.Like rollback
segment ran out of space, lot of archiving, temp segment full, so on....i tried all append, parallel hints
to accomplish....
Can you suggest me some other ways to do this?
This is kind of critical.
Thanks
June 13, 2005 - 11:41 am UTC
how did you try to change it, perhaps that would help me understand what you are doing.
You cannot change the datatype of a column that has a NON NULL value so you must not have been using "alter" and your concept of "change the datatype", needs to be defined.
Lets say we wanted to change the datatype of empno from number to varchar2...
ops$tkyte@ORA9IR2> create table emp as select empno, ename, job from scott.emp;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table emp modify empno varchar2(10);
alter table emp modify empno varchar2(10)
*
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table new_emp as
2 select cast( to_char(empno) as varchar2(20) ) empno, ename, job from emp;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table emp;
Table dropped.
ops$tkyte@ORA9IR2> rename new_emp to emp;
Table renamed.
Now, of course all of that can be done without redo, without undo. And 13gig is pretty small, won't take long to copy (but the indexes and constraints and such could take a while)
Which one is most efficient approach ???.
A reader, September 27, 2006 - 2:19 am UTC
Hi Tom,
We are using oracle 8i for our datawarehousing application.
We have 40 tables in our DW which contain one common column of numeric datatype. All these tables contain more than 20 million records.
Now, we were asked to alter the datatype of this column to varchar2 for all the tables.
I could see find some approaches discussed in this page.
Could you please suggest which can be most appropriate in our case?
What could be the risks involved with each of them?
Thanks in advance.
September 27, 2006 - 5:02 am UTC
as the move from number to varchar2 will necessarily double the storage required for this field - many rows would likely migrate.
So, I would be tempted to use a create table as select, drop the old table, rename new table and then index it.
That would be to avoid row migration.
It can be done nologging, parallel of course.
Change multiple columns at a shot
Hitesh, June 06, 2012 - 9:29 am UTC
Hello Tom,
I have to convert multiple columns from VARCHAR2 to CLOB. There are around 50 columns and tables are very huge.
To reduce downtime i am looking for any script by which together i can convert them to CLOB. Please suggest.
Thanks
June 06, 2012 - 11:31 am UTC
I would use a create table as select or dbms_redefinition to do this, not multiple alters.
A reader, January 25, 2013 - 5:33 am UTC
Why Oracle is so strict about altering columns with data presnt in the table.
Prashanti, May 07, 2015 - 7:19 am UTC
Hi Tom,
I have a column with current type as VARCHAR2 with all numeric values and I want to alter the type to NUMBER. As mentioned , if I know for sure that all values are numeric and altering will not result in any inconsistency at all , still I have to do all the workaround of creating another table/ column and copying the value and then altering.
What might be the reason that Oracle is so stringent about it, or is there any way where we can specify to force alter it.
Please let me know.
Thanks,
Prashanti