X 将 long 字段改为 lob 字段的方法

将 long 字段改为 lob 字段的方法。



create table t1 (
c1 number,
c2 long)
;

create table t2 (
c1 number,
c2 clob)
;

insert into t2 select 
c1, to_lob(c2) 
from t1 ;

create table t2 
as select 
c1, to_lob(c2) c2
from t1 ;


---------------------------------------------------------------------------------------------------------------------------



下面为insert 的测试：

插入 long 数据的字段


insert into t1(c1,c2) values(1,'Thread 1 advanced to log sequence 4933 (LGWR switch)
  Current log# 1 seq# 4933 mem# 0: /app/oracle/oradata/baicwms/redo01.log
Sun Jun 21 23:51:00 2020
Archived Log entry 4620 added for thread 1 sequence 4932 ID 0xa50e7b34 dest 1:
Sun Jun 21 23:58:09 2020
Thread 1 cannot allocate new log, sequence 4934
Private strand flush not complete
  Current log# 1 seq# 4933 mem# 0: /app/oracle/oradata/baicwms/redo01.log
Thread 1 advanced to log sequence 4934 (LGWR switch)
  Current log# 2 seq# 4934 mem# 0: /app/oracle/oradata/baicwms/redo02.log
Sun Jun 21 23:58:12 2020
Archived Log entry 4621 added for thread 1 sequence 4933 ID 0xa50e7b34 dest 1:
Mon Jun 22 02:14:59 2020
ALTER SYSTEM ARCHIVE LOG
Mon Jun 22 02:14:59 2020
Thread 1 advanced to log sequence 4935 (LGWR switch)
  Current log# 3 seq# 4935 mem# 0: /app/oracle/oradata/baicwms/redo03.log
Archived Log entry 4622 added for thread 1 sequence 4934 ID 0xa50e7b34 dest 1:
Mon Jun 22 10:07:06 2020
Thread 1 advanced to log sequence 4936 (LGWR switch)
  Current log# 1 seq# 4936 mem# 0: /app/oracle/oradata/baicwms/redo01.log
Mon Jun 22 10:07:06 2020
Archived Log entry 4623 added for thread 1 sequence 4935 ID 0xa50e7b34 dest 1:
Mon Jun 22 14:12:16 2020
Thread 1 advanced to log sequence 4937 (LGWR switch)
  Current log# 2 seq# 4937 mem# 0: /app/oracle/oradata/baicwms/redo02.log
Mon Jun 22 14:12:16 2020
Archived Log entry 4624 added for thread 1 sequence 4936 ID 0xa50e7b34 dest 1:
Mon Jun 22 16:21:10 2020
***********************************************************************
Fatal NI connect error 12170.
  VERSION INFORMATION:
  TNS for Linux: Version 11.2.0.1.0 - Production
  Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 22-6月 -2020 16:21:10
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
TNS-12535: TNS: 操作超时
    ns secondary err code: 12560
    nt main err code: 505  
TNS-00505: 操作超时
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.20.100.197)(PORT=65182))
Mon Jun 22 16:33:22 2020
***********************************************************************
Fatal NI connect error 12170.
  VERSION INFORMATION:
  TNS for Linux: Version 11.2.0.1.0 - Production
  Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
  Time: 22-6月 -2020 16:33:22
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535   
TNS-12535: TNS: 操作超时
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: 操作超时
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.20.100.197)(PORT=65390))
Mon Jun 22 16:41:06 2020
**********************************************************************
Fatal NI connect error 12170.');



======================================================================================================================================
======================================================================================================================================

-------------------------------------------------------------------------------------
只能用 create 与 insert 连接。
不能用 select to_lob(c2) from t1;



SQL> select 
c1, to_lob(c2) 
from t1 ; 

c1, to_lob(c2)
    *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected - got LONG




SQL> insert into t2 select 
c1, to_lob(c2) 
from t1 ; 

2 rows created.

SQL> 
SQL> 
SQL> commit;

Commit complete.







======================================================================================================================================
======================================================================================================================================