Oracle SQL to Change Column Type from Number to Varchar2 While It Contains Data

Oracle SQL to change column type from number to varchar2 while it contains data

create table temp_uda1 (test1 integer);
insert into temp_uda1 values (1);

alter table temp_uda1 add (test1_new varchar2(3));

update temp_uda1 
   set test1_new = to_char(test1);

alter table temp_uda1 drop column test1 cascade constraints;
alter table temp_uda1 rename column test1_new to test1;

If there was an index on the column you need to re-create it.

Note that the update will fail if you have numbers in the old column that are greater than 999. If you do, you need to adjust the maximum value for the varchar column

How to change number(7,2) to varchar in oracle?

You should use modify keyword instead of your second alter .

alter table
   emp
modify
(
   sal    varchar2(10)
);

When modifying a tables column datatype , you need to use modify keyword.

Of course, you must deal with existing column data. When modifying a tables column datatype you may want to export the rows, redefine the table and then re-import you data.

In this you would need to follow these steps to alter a column data type:

1. Create the new column at the end of the table.
2. Run an update to populate the new table column
3. Drop the old table column
4. Re-name the new column to the original column name

Oracle: How to change column data type from VARCHAR to NUMBER without losing data

Oracle does not allow modification of data type of the column if it is not empty so as a workaround, You need to follow the following steps

1. create another column with NUMBER data type let's say "NUMBER1".
2. add the data of "NUMBER" column into that newly created column("NUMBER1").
3. Remove the original "NUMBER" column
4. Rename the newly created column "NUMBER1" to "NUMBER"

as following:

Oracle setup:

SQL> CREATE TABLE YOUR_TABLE (
  2      ID         NUMBER(10, 0),
  3      "NUMBER"   VARCHAR(255)
  4  );

Table created.

SQL> DESC YOUR_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NUMBER                                             VARCHAR2(255)

SQL> INSERT INTO YOUR_TABLE VALUES (1,'1');

1 row created.

SQL> COMMIT;

Commit complete.

Showing error if the column data type is changed directly:

SQL> ALTER TABLE YOUR_TABLE MODIFY "NUMBER" NUMBER;
ALTER TABLE YOUR_TABLE MODIFY "NUMBER" NUMBER
                              *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

Solution:

SQL> ALTER TABLE YOUR_TABLE ADD "NUMBER1" NUMBER;

Table altered.

SQL>
SQL> UPDATE YOUR_TABLE
  2  SET "NUMBER1" = "NUMBER";

1 row updated.

SQL>
SQL> ALTER TABLE YOUR_TABLE DROP COLUMN "NUMBER";

Table altered.

SQL>
SQL> ALTER TABLE YOUR_TABLE RENAME COLUMN "NUMBER1" TO "NUMBER";

Table altered.

SQL> DESC YOUR_TABLE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 NUMBER                                             NUMBER

SQL>

Cheers!!

Fastest way to change column type from varchar2 to number on large table in Oracle

I guess, you can use parallel operations:

Parallel DML

Let your table support parallel operations:

ALTER TABLE EFF parallel;

Then you perform all your steps as you describe in the question.

Parallel DDL + CTAS

CREATE TABLE temp_table_EFF
PARALLEL
AS
SELECT MOD, MP, PROG, TO_NUMBER(RNK), EFF_PART
  FROM EFF 

Then you drop your table:

DROP TABLE EFF;

Then you rename your temp table:

ALTER TABLE temp_table_EFF RENAME TO EFF;

Then you add indexes for to this new table:

ALTER TABLE EFF ADD CONSTRAINT PK_EFF PRIMARY KEY (MOD, MP, PROG, NRNK);

I have no tables with 30M records, so I can't tell you what method is faster. Try both. If you think that there won't be decent performance improvement try to use the NOLOGGING option to forbid your table generate the redo information.

How to change the data type of column in oracle

As far as concerns, there is no need for this complex logic. Oracle lets you extend the width of a varchar column with the following command:

ALTER TABLE master_history MODIFY mycol VARCHAR2(300);
                               --^-- modify this to your real column name

Extending the width of the column does not affect existing data.

How to modify column data-type while column is in used

You can change the data type of a column online using dbms_redefinition

create table t (
  c1 varchar2(10)
    primary key
);

create table t_new (
  c1 number(10, 0)
    primary key
);

insert into t values ( '1.0000' );
commit;

begin 
  dbms_redefinition.start_redef_table ( 
    user, 't', 't_new', 
    col_mapping => 'to_number ( c1 ) c1', 
    options_flag => dbms_redefinition.cons_use_rowid 
  );
end;
/
exec dbms_redefinition.sync_interim_table ( user, 't', 't_new' );
exec dbms_redefinition.finish_redef_table ( user, 't', 't_new' );

desc t

Name   Null?      Type         
C1     NOT NULL   NUMBER(10)

select * from t;

C1   
    1 

There are also options to copy constraints, triggers, indexes, etc. automatically in this process.

---

Related Topics