Skip to Main Content
  • Questions
  • Best way to change the datatype of a column in a Table with 20 million

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prasad.

Asked: January 13, 2017 - 4:43 pm UTC

Last updated: February 23, 2021 - 6:01 am UTC

Version: Oracle 11g

Viewed 10K+ times! This question is

You Asked

Hi Team,

Wish you a happy new Year. Welcome to Maria Colgan.

I have a requirement to change the datatype of a column of number type to Varchar2(35) and then rename the table. The table has huge data around 20 millions. Since already data is there in the table I can't change the datatype without making null to the existing data in that column. Since I don't want to loose the data what is the better approach to modify the data type of that column. Sample code has given below.

create table t22(sno number,sname number,marks varchar2(5),constraint pk primary key (sno));

insert into t22 values(1,36,'ppc');
insert into t22 values(2,3466,'c');
insert into t22 values(3,356,'zpppc');
insert into t22 values(4,3226,'xc');
insert into t22values(5,366567,'abc');
---------
Lets say the table t22 has huge data now

Now I have to change the sname from number to varchar2(35)

Option1: add a new column sname2 varchar2(35) to T22 ,then update the table T22 as
update T22 set sname2=sname;Rename T22 to T_new_name;
then drop sname column and then rename sname2 to sname ---- In this approach I am worrying of UNDO generation and time taken to updation of 20 millions rows.

Option2: create table T_new_name as select sno,to_char(sname) sname,marks from t22;
drop table t22;

Create constraints, Indexes on T_new_name and gather stats.

In this approach I need to re create the constraints, Indexes again. And need to build the stasts again.

So kindly suggest me which approach is the better way or any other approach to fulfill my requirement in best way (time and performance).

Thanks in Advance.

and we said...

You can do the whole thing using DBMS_REDEFINITION and not even have an outage.

Here's an example of a table with some indexes, triggers etc... and we'll change one of the data types using the column mapping facility.


SQL> create table t as select * from scott.emp;

Table created.

SQL>
SQL> alter table t add primary key (empno);

Table altered.

SQL>
SQL> alter table t add constraint CK check ( sal >= 0 );

Table altered.

SQL>
SQL> create or replace
  2  trigger t_TRG
  3  before insert on t
  4  for each row
  5  begin
  6    :new.sal := :new.sal * 10 ;
  7  end;
  8  /

Trigger created.

SQL>
SQL> create table new_t (
  2     EMPNO                                              NUMBER(4)
  3    ,ENAME                                              VARCHAR2(10)
  4    ,JOB                                                VARCHAR2(9)
  5    ,MGR                                                NUMBER(4)
  6    ,HIREDATE                                           DATE
  7    ,SAL                                                NUMBER(7,2)
  8    ,COMM                                               NUMBER(7,2)
  9    ,DEPTNO                                             varchar2(20)  -- CHANGED
 10  );

Table created.

SQL>
SQL> exec dbms_redefinition.can_redef_table(user, 'T');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.start_redef_table(user, 'T', 'NEW_T', col_mapping=>'empno,ename,job,mgr,hiredate,sal,comm,to_char(deptno) deptno');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.sync_interim_table(user, 'T', 'NEW_T');

PL/SQL procedure successfully completed.

SQL>
SQL> set serveroutput on
SQL> declare
  2    l_err pls_integer;
  3  begin
  4    dbms_redefinition.copy_table_dependents(
  5      uname               => user,
  6      orig_table          => 'T',
  7      int_table           => 'NEW_T',
  8      num_errors          => l_err
  9      );
 10
 11    dbms_output.put_line('errors=' || l_err);
 12  end;
 13  /
errors=0

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_redefinition.finish_redef_table(user, 'T', 'NEW_T');

PL/SQL procedure successfully completed.

SQL> drop table new_t purge;

Table dropped.

SQL> select object_name, status
  2  from user_objects
  3  where created > sysdate - 1/24;

OBJECT_NAME                              STATUS
---------------------------------------- -------
T_TRG                                    INVALID
SYS_C0032571                             VALID
T                                        VALID

3 rows selected.

SQL> alter trigger t_trg compile;

Trigger altered.

SQL>


and voila...the job is done

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, January 23, 2017 - 10:36 pm UTC

I followed the above suggested Process. I am successfully able to change the datatype of the table. But it took 30 minutes for 4 millions rows data and I need to change the table with 20 millions rows. So is there any other way that we can reduce the time.

Thank you.
Connor McDonald
January 24, 2017 - 12:37 am UTC

Yes but with DBMS_REDEFINTION the duration *does not matter*.

Because the system is *available* whilst you do the change.

Consider virtual column

Gh.., January 24, 2017 - 7:49 am UTC

If the values of sname are always numeric consider adding virtual column as to_char of ...

A reader, January 24, 2017 - 4:07 pm UTC

I am trying in other way as

Create t_new as select sno,to_char(sname) sname,marks from t22;

But I am getting below error in 12c. My original table is having CLOB column. But here in table t22 I did not provide that column.
(It is working fine in 11g but my database is migrated to 12c)

Error: SQL Error: ORA-60019: Creating initial extent of size 14 in tablespace of extent size 8,

I am able to create the table with CLOB data type in 12c if I specify the storage parameters but CTAS we can't specify the storage parameters, column datatypes.
Connor McDonald
January 25, 2017 - 1:47 am UTC

It might be related to this:

https://connormcdonald.wordpress.com/2015/11/20/clobs-from-11g-to-12c/

securefiles becomes the default, and securefiles must have extents at least 1m

A reader, January 25, 2017 - 3:04 pm UTC

Hi Connor McDonald,

Thanks for your link. even I read the document I did not understand anything. If you don't mind and if I can get your valuable time could you please give me little more details regarding the LOB types restrictions from 11g to 12c.
Connor McDonald
January 26, 2017 - 11:47 pm UTC

Here's a nice article introducing securefiles in 11g

http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html


how about when duration does matter

Edward Hayrabedian, September 11, 2018 - 6:05 pm UTC

hi,

the online redefinition is not panacea. i am talking about cases when the application release has to be alligned with the database migration. then we have to make the migrtion as fast as possible.. so the original.question is still not answered ... i.e. which is the fastest way to alter a column full with data?

thanks in advance!!
Connor McDonald
September 16, 2018 - 3:08 am UTC

i am talking about cases when the application release has to be aligned with the database migration

There's a difference between how long DBMS_REDEFINTION takes from start to finish to the time at which you need an outage. When you use DBMS_REDEFINTION do a:

- a start command
- intermittent sync commands
- a finish command

You only have application unavability during the "finish" component, at which point you would deploy your application changes. If the start/syncs take 8 hours it does *not* impact your availability.

Here's an example of its use



Redefinition over multiple days

Narendra, February 10, 2021 - 5:10 pm UTC

Hello Connor,

Thanks (once again) for your detailed responses, supported by test cases.
I am planning to use DBMS_REDEFINITION on modifying a large table in our database, mainly due to concerns about the "downtime" with "offline" approach. I had a couple of questions about this
1. I am planning to start the REDEF about a week before the actual "event" and am planning to introduce multiple SYNCs during weekdays, with FINISH to be done during the "event". What challenges, if any, do you see with this approach?
a) How will DB going down/ is restarted during weekdays, affect REDEF? I am guessing no impact but happy to be proved wrong
b) Is there a way to monitor the progress of any of the REDEF operations? I believe 12.2. provides a view v$online_redef but is there any way to monitor REDEF on 11.2?

Thanks,
Connor McDonald
February 16, 2021 - 3:57 am UTC

1. I am planning to start the REDEF about a week before the actual "event" and am planning to introduce multiple SYNCs during weekdays, with FINISH to be done during the "event". What challenges, if any, do you see with this approach?


The big question really is volume. You can think of the SYNC as being like a materialized view refresh using mview logs. So ideally, less volume (ie, a quiet time) is ideal. And don't forget to factor in the time for subordinate objects (indexes, constraints etc)

a) How will DB going down/ is restarted during weekdays, affect REDEF? I am guessing no impact but happy to be proved wrong


Shouldn't make any difference, but I strongly encourage you to test in your own environment!

SQL> conn /@db19_pdb1 as sysdba
Connected.
SQL> create table scott.old_emp as select * from scott.emp;

Table created.

SQL> alter table scott.old_emp add primary key ( empno );

Table altered.

SQL> create table scott.new_emp as select * from scott.emp where 1=0;

Table created.

SQL>
SQL> EXEC DBMS_REDEFINITION.start_redef_table('SCOTT', 'OLD_EMP', 'NEW_EMP');

PL/SQL procedure successfully completed.

SQL>
SQL> EXEC DBMS_REDEFINITION.sync_interim_table('SCOTT', 'OLD_EMP', 'NEW_EMP');

PL/SQL procedure successfully completed.

SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> EXEC DBMS_REDEFINITION.sync_interim_table('SCOTT', 'OLD_EMP', 'NEW_EMP');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_REDEFINITION.finish_redef_table('SCOTT', 'OLD_EMP', 'NEW_EMP');

PL/SQL procedure successfully completed.



b) Is there a way to monitor the progress of any of the REDEF operations? I believe 12.2. provides a view v$online_redef but is there any way to monitor REDEF on 11.2?

The first one is equivalent to a big CTAS, so you should be able to use v$session_longops or sql monitoring if you are licensed for it.

Re: Redefinition over multiple days

Narendra, February 17, 2021 - 11:23 am UTC

Thank you Connor.
In terms of SYNC, we are looking at volumes that could range from few hundred thousands and up to a million. So hopefully, SYNC should not affect any BAU processing as long as we time it properly.

Hopefully, I am able to use DBMS_REDEFINITION in order to provide the benefit to the business/release. Will update here on how it went if I get the chance to use it eventually.
Connor McDonald
February 23, 2021 - 6:01 am UTC

Keep us posted - sounds like an interesting exercise.

More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here