This is the 7th day of my participation in the August More Text Challenge

We already have data in the table. How to convert the varCHAR2 type into CLOB type?

Me: Why switch to CLOB? Don’t use it if you can.

Brother: The varchar2 is long enough.

Me: 4000 is not enough?

Brother: The hospital needs to save a brief introduction, just like the front page of the hospital website. The length is over 4000.

Me: Let me tidy it up for you.

Goal:

Change the data type of the name field in test from VARCHAR2 to CLOB.

Test table environment:

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                       VARCHAR2(4000)
 REMARK                                     VARCHAR2(46)
 
SQL> select count(id) from test;
 COUNT(ID)
----------
    300000
Copy the code

Backup data:

The data pump exports DMP files or creates backup tables in the library.

SQL> create table test_20200805 as select * from test3; \ Table created.Copy the code

Start conversion:

Method 1: Replace fields

1) Change the name of the field to name_bak
SQL> alter table test rename column name to name_bak;
Table altered.

SQL> desc TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                              NUMBER
 NAME_BAK                                 VARCHAR2(4000)
 REMARK                                     VARCHAR2(46)
Copy the code
2) Add cloB field name
SQL> alter table test add name clob;
Table altered.

SQL> desc TEST
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                              NUMBER
 NAME_BAK                                 VARCHAR2(4000)
 REMARK                                     VARCHAR2(46)
 NAME                                               CLOB
Copy the code
3) Update data to CLOB fields
SQL> update test set name=name_bak; \ Elapsed: 00:01:45.60 SQL> select Elapsed; \ Elapsed: 00:00.01 \ Commit complete.\ Elapsed: 00:00:00.01Copy the code
4) Delete name_bak from the database
SQL> alter table test drop column name_bak; \ Table Altered.\ Elapsed: 00:00:42.58Copy the code
5) Table structure, table data verification
SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER REMARK VARCHAR2(46) NAME CLOB SQL> select * from test; ID REMARK NAME ---------- ---------------------------------------------- -------------------------------------------------------------------------------- 38706 38706remark 38706name 38707 38707remark 38707name 38708 38708remark 38708name 38709 38709remark 38709name 38710 38710remark 38710name 38711 38711remark 38711name 38712 38712remark 38712name 38713 38713remark 38713name 38714 38714remark 38714name 38715 38715remark 38715name 38716 38716remark 38716name 38717 38717remark 38717name 38718 38718remark 38718name 38719 38719remark 38719name 38721 38721remark 38721name 38725 38725remark 38725name 38728 38728remark 38728name 38732 38732remark 38732name 38735 38735remark 38735name 38739 38739remark 38739name ..................... .Copy the code

Method 2: Replace the table

1) Rename table test to test_bak
SQL> rename test to test_bak; \ Table standing.\ Elapsed: 00:00:00.21Copy the code
2) Create a new table using the following statement
SQL> create table test as select id,to_clob(name) name,remark from test_bak; \ Table Created.\ Elapsed: 00:00:01.45Copy the code
3) Table structure, table data verification
SQL> desc test Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER NAME CLOB REMARK VARCHAR2(46) SQL> select * from test where rownum<=100; ID NAME REMARK ---------- -------------------------------------------------------------------------------- ---------------------------------------------- 196304 196304name 196304remark 196305 196305name 196305remark 196306 196306name 196306remark 196307 196307name 196307remark 196308 196308name 196308remark 196309 196309name 196309remark 196310 196310name 196310remark 196311 196311name 196311remark 196312 196312name 196312remark 196313 196313name 196313remark 196314 196314name 196314remark 196315 196315name 196315remark 196316 196316name 196316remark 196317 196317name 196317remark 196318 196318name 196318remark 196319 196319name 196319remark ................ .Copy the code