How to handle data loss during alter table process

[zircon06]

I have table host which contain version column with int now I'm changing column data type to varchar2. Can give me example how to data loss during alter data type process

Thanks in advance

 

[SantaMufasa]

Zircon,

Here is what I interpret from your specifications:

create table host (version int);

Table created.

SQL> desc host
 Name                    Null?    Type
 ----------------------- -------- ----------
 VERSION                          NUMBER(38)

SQL> insert into host values (12345);

1 row created.

SQL> select * from host;

   VERSION
----------
     12345

alter table host modify version varchar2(25)
                        *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Notice that you cannot change an INT/NUMBER column datatype if the column contains data. Therefore, you can achieve your objective via the following scenario:

alter table host add tempcol varchar2(25);

Table altered.

SQL> update host set tempcol = version, version = null;

1 row updated.

SQL> select * from host;

   VERSION TEMPCOL
---------- -------------------------
           12345

SQL> alter table host modify version varchar2(25);

Table altered.

SQL> update host set version = tempcol;

1 row updated.

SQL> alter table host drop column tempcol;

Table altered.

SQL> desc host
 Name                    Null?    Type
 ----------------------- -------- ----------------
 VERSION                          VARCHAR2(25)

SQL> select * from host;

VERSION
-------------------------
12345

So, this illustrates how to achieve your objective.

I don't understand what you mean when you say:

Can give me example how to data loss during alter data type process

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]