oracle 数据库 lob 字段之 clob 存储

最新推荐文章于 2022-08-22 11:43:09 发布

数据库人生

最新推荐文章于 2022-08-22 11:43:09 发布

阅读量1.2k

点赞数

分类专栏： # oracle table index 文章标签： lob clob basicfile 4000 1982

本文链接：https://blog.csdn.net/ctypyb2002/article/details/104609441

版权

oracle table index 专栏收录该内容

39 篇文章 2 订阅

订阅专栏

os: centos 7.4
db: oracle 11.2.0.4

版本

# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
# 
# su - oracle
Last login: Tue Jan 21 03:40:05 CST 2020 on pts/0
$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.4.0 Production on Mon Feb 3 10:29:09 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> set lines 300;
SQL> set pages 300;
SQL> 
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE	11.2.0.4.0	Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL>

create table

SQL> set lines 300;
set pages 300;

SQL> 
SQL> create table tmp_t0(
  id   integer,
  name varchar2(100),
  memo clob
)
;

SQL> SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','TMP_T0','SCOTT2')) FROM DUAL;

TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','TMP_T0','SCOTT2'))
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  CREATE TABLE "SCOTT2"."TMP_T0"
   (	"ID" NUMBER(*,0),
	"NAME" VARCHAR2(100),
	"MEMO" CLOB
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"
 LOB ("MEMO") STORE AS BASICFILE (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING )

请注意最后面对 MEMO 列的描述,默认行为是启用行内存储（ENABLE STORAGEIN IN ROW）
LOB (“MEMO”) STORE AS BASICFILE (
TABLESPACE “USERS” ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )

SQL> col table_name format a30;
col column_name format a30;
col SEGMENT_NAME format a60;

SQL> select ul.TABLE_NAME,ul.COLUMN_NAME,ul.SEGMENT_NAME,ul.TABLESPACE_NAME,
       ul.retention,ul.CACHE,ul.IN_ROW,ul.SECUREFILE,ul.chunk,ul.LOGGING
       --ul.*
from user_lobs ul
where 1=1
;

TABLE_NAME		       COLUMN_NAME		      SEGMENT_NAME						   TABLESPACE_NAME		   RETENTION CACHE	IN_ SEC      CHUNK LOGGING
------------------------------ ------------------------------ ------------------------------------------------------------ ------------------------------ ---------- ---------- --- --- ---------- -------
TMP_T0			       MEMO			      SYS_LOB0000479841C00003$$ 				   USERS			    900 NO 	YES NO	      8192 YES


SQL> select *
from user_segments us
where 1=1
and us.segment_name in (
 'TMP_T0',
 'SYS_LOB0000479841C00003$$'
);

no rows selected

insert into

在网上搜索 lob 字段设置 ENABLE STORAGE IN ROW 后，在写入字节大于4000（字符大于 1982）时，会单独分配 lob 存储空间。
1982*2+36=4000

SQL> declare 
  lv_name varchar2(30000);
begin
  for c_f in (
    select level as id,
           '我' as name
      from dual
     connect by level <=1982
  )
  loop
     lv_name:=lv_name||c_f.name;
     insert into TMP_T0
     select c_f.id,c_f.name,lv_name from dual
     ;
     commit;
  end loop;

end;
/

ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 12

看报错信息是 lv_name 在拼接到一定长度字符串时转换成了 LONG 类型，然后插入 CLOB 字段

这个报错可能有以下几种原因：
1、插入到字符串长度大于4000字节。
2、插入到表中的记录的某个字段数据的实际长度大于2000个字节（如果是UTF-8，则是1333个字节）；或者是插入的记录中有两个或两个以上长度大于2000字节的字符串。

把 lv_name 换成 clob 试下，执行成功。

SQL> declare 
  lv_name clob;
begin
  for c_f in (
    select level as id,
           '我' as name
      from dual
     connect by level <=1982
  )
  loop
     lv_name:=lv_name||c_f.name;
     insert into TMP_T0
     select c_f.id,c_f.name,lv_name from dual
     ;
     commit;
  end loop;

end;
/

参考:
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45267