Changing the datatype of a column from VARCHAR2 to CLOB

Some days ago, I realized I hadn’t estimated correctly the size of a column of a table that receives data from an external source. I defined a VARCHAR2(4000 CHAR) datatype but it came a longer message  (I got an ORA-01461 error), so I decided to move the column to a CLOB datatype.

At first sight, it seems a simple matter. For example, if you have the following table definition:

   create table test_messages
   (message_id number             primary key,
    content   varchar2(4000 char) not null);

You would think to issue the following statement:

   alter table test_messages
   modify (content clob not null);

But, if you do that, you get an ORA-22296 error.

I had several options to solve the problem (please review the reference of this post), but I finally selected the following one:

   alter table test_messages
   add (temp_content clob);

   update test_messages
   set temp_content = content;

   alter table test_messages
   drop column content;

   alter table test_messages
   rename column temp_content to content;

   alter table test_messages
   modify (content not null);

Anyway, it’s very important to review all the application code affected by the change, because, for example, if you use the substr function, you have to change it to dbms_lob.substr. On the other hand, if you use JPA and you have an entity like:

   @Entity
   @Table(name = "TEST_MESSAGES")
   public class TestMessages implements Serializable {

       @Id
       @Column(name = "MESSAGE_ID")
       private Long messageId;

       @Column(name = "CONTENT", length = 4000, nullable = false)
       private String content;

       public Long getMessageId() {
           return messageId;
       }

       public void setMessageId(Long messageId) {
           this.messageId = messageId;
       }

       public String getContent() {
           return content;
       }

       public void setContent(String content) {
           this.content = content;
       }

   }

You have to change the definition of content to:

   @Lob
   @Column(name = "CONTENT", nullable = false)
   private String content;

Finally, I’d like to point out two issues that could affect to performance. The first one is the possibility of defer the removing of the initial column, running the following statement:

   alter table test_messages
   set unused column content;

instead of:

   alter table test_messages
   drop column content;

Later, in a period of low use of the database, you can issue:

   alter table test_messages
   drop unused columns;

The other thing regarding to performance is that it’s convenient reorganizing the storage of the table by running the following command:

   alter table test_messages move;

Statements like alter index <index_name> rebuild could also be needed.


References

Advertisements

2 Comments on “Changing the datatype of a column from VARCHAR2 to CLOB”

  1. Ed Putkonen says:

    Don’t forget that with Oracle you can use the DBMS_REDEFINTION package to execute this type of change. The redefinition process is particularly useful when you need to change data type on an active online table where holding locks for a large update might be impractical. It also removes the ned to drop the column, with the ensuing table lock.

    • fcosfc says:

      Hi Ed,

      Thank you very much for pointing out other option, you also can find out that option following the reference I give on the article.

      Anyway, I just want to describe the option I took and it ran properly on my case. Other cases would require other options, maybe your solution is better for a problem with millions of rows involved.

      I’m a curious person, so I have prepared the following test case using dbms_redefinition package:

      create table test_messages_2
      (message_id number primary key,
      content clob not null);

      exec dbms_redefinition.start_redef_table( user, ‘TEST_MESSAGES’, ‘TEST_MESSAGES_2’, ‘message_id, to_clob(content) content’ );

      exec dbms_redefinition.finish_redef_table( user, ‘TEST_MESSAGES’, ‘TEST_MESSAGES_2’ );

      drop table test_messages_2;

      And I’ve compared with the one I explain on the post, using the package runstats_pkg of Tom Kyte and Adrian Billington. Here you have the complete script:

      set SERVEROUTPUT on

      drop table test_messages;
      drop sequence test_sq;

      create table test_messages
      (message_id number primary key,
      content varchar2(4000 char) not null);

      create sequence test_sq
      start with 1
      minvalue 1
      maxvalue 999999999999;

      begin
      for i in 1..1000
      loop
      insert into test_messages
      values (test_sq.nextval, rpad(‘*’, 4000, ‘*’));
      end loop;
      commit;
      end;
      /

      exec runstats_pkg.rs_start;

      alter table test_messages
      add (temp_content clob);

      update test_messages
      set temp_content = content;

      alter table test_messages
      drop column content;

      alter table test_messages
      rename column temp_content to content;

      alter table test_messages
      modify (content not null);

      exec runstats_pkg.rs_pause;

      drop table test_messages;
      drop sequence test_sq;

      create table test_messages
      (message_id number primary key,
      content varchar2(4000 char) not null);

      create sequence test_sq
      start with 1
      minvalue 1
      maxvalue 999999999999;

      begin
      for i in 1..1000
      loop
      insert into test_messages
      values (test_sq.nextval, rpad(‘*’, 4000, ‘*’));
      end loop;
      commit;
      end;
      /

      exec runstats_pkg.rs_resume;

      create table test_messages_2
      (message_id number primary key,
      content clob not null);

      exec dbms_redefinition.start_redef_table( user, ‘TEST_MESSAGES’, ‘TEST_MESSAGES_2’, ‘message_id, to_clob(content) content’ );

      exec dbms_redefinition.finish_redef_table( user, ‘TEST_MESSAGES’, ‘TEST_MESSAGES_2’ );

      drop table test_messages_2;

      exec runstats_pkg.rs_stop(1000);

      And the results:

      ===============================================================================================
      RunStats report : 12-JUL-2013 17:15:28
      ===============================================================================================

      ———————————————————————————————–
      1. Summary timings
      ———————————————————————————————–

      Type Name Run1 Run2 Diff
      —– ————————————————– ———— ———— ————
      TIMER cpu time (hsecs) 90 98 8
      TIMER elapsed time (hsecs) 5,230 5,520 290

      Comments:
      1) Run1 was 5,3% quicker than Run2
      2) Run1 used 5,3% less CPU time than Run2

      ———————————————————————————————–
      2. Statistics report
      ———————————————————————————————–

      Type Name Run1 Run2 Diff
      —– ————————————————– ———— ———— ————
      STAT no work – consistent read gets 3,416 2,106 -1,310
      STAT table scan blocks gotten 3,183 1,494 -1,689
      LATCH cache buffers lru chain 4,915 6,613 1,698
      STAT commit cleanouts 2,092 270 -1,822
      STAT commit cleanouts successfully completed 2,092 270 -1,822
      STAT calls to kcmgas 2,109 263 -1,846
      STAT buffer is not pinned count 3,404 1,547 -1,857
      STAT deferred (CURRENT) block cleanout applications 2,076 169 -1,907
      LATCH simulator hash latch 3,356 5,264 1,908
      LATCH simulator lru latch 2,312 4,252 1,940
      LATCH shared pool 475 2,424 1,949
      STAT switch current to new buffer 2,005 19 -1,986
      LATCH redo allocation 6,957 4,569 -2,388
      LATCH library cache lock 977 3,392 2,415
      STAT free buffer requested 2,622 187 -2,435
      STAT redo entries 6,837 4,389 -2,448
      LATCH library cache pin 1,461 4,371 2,910
      STAT sorts (rows) 51 3,177 3,126
      LATCH row cache objects 1,463 5,352 3,889
      STAT recursive calls 4,306 8,614 4,308
      STAT table scan rows gotten 3,351 7,950 4,599
      STAT session logical reads 22,843 17,832 -5,011
      STAT db block gets 17,116 11,891 -5,225
      STAT db block gets from cache 16,116 10,889 -5,227
      LATCH library cache 2,451 8,156 5,705
      LATCH session allocation 166 6,014 5,848
      STAT db block changes 13,509 6,502 -7,007
      STAT IMU undo allocation size 380 16,600 16,220
      STAT physical write bytes 8,192,000 8,208,384 16,384
      STAT physical write total bytes 8,192,000 8,208,384 16,384
      STAT IMU Redo allocation size 340 19,040 18,700
      LATCH cache buffers chains 71,476 52,064 -19,412
      STAT physical read bytes 0 32,768 32,768
      STAT physical read total bytes 0 32,768 32,768
      STAT session pga memory -1,572,864 -131,072 1,441,792
      STAT undo change vector size 4,476,892 216,632 -4,260,260
      STAT redo size 13,747,028 9,023,956 -4,723,072

      ———————————————————————————————–
      3. Latching report
      ———————————————————————————————–

      Type Name Run1 Run2 Diff
      —– ————————————————– ———— ———— ————
      LATCH total latches used 116,936 125,011 8,075

      Comments:
      1) Run1 used 6,5% fewer latches than Run2

      ———————————————————————————————–
      4. Time model report
      ———————————————————————————————–

      Type Name Run1 Run2 Diff
      —– ————————————————– ———— ———— ————
      TIME sequence load elapsed time 509 468 -41
      TIME repeated bind elapsed time 110 1,126 1,016
      TIME failed parse elapsed time 0 21,941 21,941
      TIME DB CPU 993,251 1,022,221 28,970
      TIME hard parse elapsed time 20,536 73,660 53,124
      TIME parse time elapsed 30,335 115,367 85,032
      TIME PL/SQL execution elapsed time 7,655 445,958 438,303
      TIME DB time 51,149,203 53,929,342 2,780,139
      TIME sql execute elapsed time 50,970,619 53,862,464 2,891,845

      ———————————————————————————————–
      5. About
      ———————————————————————————————–
      – RunStats v2.01 by Adrian Billington (http://www.oracle-developer.net)
      – Based on the original RUNSTATS utility by Tom Kyte

      ===============================================================================================
      End of report
      ===============================================================================================


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s