lob类型是什么意思(lob类型参数)

>当数据表中包括lob类型的数据列时，也会有独特的段对象建立。常规的数据段之外，另外增加了两个明显是系统命名的段对象，类型分别为lobsegment和lobindex。

对Oracle lob类型数据表而言，一个带lob列的数据表创建是要对应多个数据段创建的。除了传统的数据表创建的数据段Table Data Segment之外，一个lob列都会生成两个专门的段：lob段和lob索引段。

Lob段（LobSegment）对应的是存放在数据表lob列上的数据。在Oracle的lob类型数据列，有两种保存位置结构。一个是in-row
storage，也就是每一行的lob数据同其他列的数据以行的形式一起保存在数据块中。这种情况的lob列取值较小。而另一种为out-of-row
storage，当lob对象较大，不能保存在一个数据块中时，可以将其放置在一个独立lobsegment中进行保存。而out-of-row
storage时数据行中lob列上保存的只是一个指向lobsegment对应位置的指针引用。

Lob索引段（LobIndex）是Oracle为每一个lob类型列强制生成的索引，主要作用是用于进行lob类型数据检索加速的操作。Lob index与lob列共生，如果强制进行删除操作，是会报错的。

SQL> drop index SYS_IL0000056069C00002$$;

drop index SYS_IL0000056069C00002$$

ORA-22864: 无法 ALTER 或 DROP LOB 索引

在实际物理设计部署过程中，经常有将大对象分区和存储单独部署表空间的情况。可以根据实际的情况，将一些很大的lob列连同索引保存在单独的表空间上。

但是注意，一般数据表而言，lob段和lob index段是在一个表空间上。即使在SQL语法上存在支持，但是将lob段和lobindex分开存储的语句通常被忽略掉。 
 

Lob与其它类型的转换:

通过TO_CLOB可以将CHAR，NCHAR，VARCHAR2,NVARCHAR2，NCLOB类型转换成CLOB；

通过TO_LOB可以将LONG RAW转换成BLOB，LONG转换成CLOB；

通过TO_NCLOB可以将CHAR，NCHAR，VARCHAR2,NVARCHAR2，CLOB转换成NCLOB。

LOB段属性:

默认情况下,当定义了含有LOB字段的table后,
oracle会自动为每个LOB创建两个段,lob segment和lob index segment. lob
segment存储了每个lob的具体的值,而lob index segment则存储了每个lob值的地址.lob segment、lob
index segment和table
segment存储在同一个表空间中.oracle为lob段提供了单独的段属性.我们在创建table时可以定义将lob和table分别存储在不同的表空间中。平常定义lob时,我们必须考虑以下几个比较重要的属性:

chunk：比oracle block
size更大的一种逻辑块,专用于LOB数据的存储,默认为db_block_size的大小,如果手动定义必须定义为db_block_size的倍数.最大不能超过32K。不合理的chunk定义不仅浪费存储空间,而且还会影响性能.那么在定义前必须了解应用,
每个LOB列的数据的平均大小,尽量减少LOB的空间浪费.看下面的表格能说明一切:

上面表格用一些数据说明了chunk跟数据之间的存储关系,更形象的说明了磁盘空间的利率问题。 红色标记的部分说明了不合理的chunk定义.必须注意到的LOB中chunk浪费的空间是不可以重用的.

disable/enable storage
in row: 默认情况下为enable storage in row, 在没有分离lob段的情况下,table中的每行数据都存储在同一个block中,这样如果lob列很大时，可能会造成严重的行链接;当lob段和table段分离的情况下,oracle会自动将小于4k的lob数据存储在table
segment,将大于4k的lob数据存储在lob段.
如果设置为 disable storage in
row的情况时,在lob段和table段分离的情况下, 不管lob数据多大,oracle都会将lob数据存储在lob段,这样就出现了上面的3500 disable storage in row 32 KB ,32 KB ,10情况,浪费了90%的存储空间.

pctversion/retention:这两个属性用来解决lob段的一致性读问题。lob的特殊性决定它不能使用undo/rollback
segment来管理自己的更新的old version,通常lob会在自己所在的表空间中划分一部分空间来管理自己的undo，保证read
consistent。
lob中更新原理是在lob segment中分配新的chunk插入新的数据,保留旧的镜像,如果一个数据有多个更新存在的话,
那么就会存在多个版本.pctversion用来定义lob segment中undo区域的大小,pctverision
是一个百分比,定义所有lob空间用来存放前镜像的百分比,如果前镜像使用空间超过这个百分比了,oracle不自动扩展这部分的大小,
会重用这些前镜像的空间.如果一个lob segment段的更新很频繁的情况下，那么该lob段的增长可能会很快.retention是9i的新参数, 只能用在tablespace采用ASSM的情况,在lob更新的时候,前镜像会保留一段时间, 具体的时间由undo_retention参数决定.采用哪种undo 方式,必须对应用测试后在决定.

nocache/cache reads/cache:定义LOB的cache方式,
                          nocache为不cache任何lob数据;
                          cache reads为在lob read的情况下cache数据;
                          cache为读写都cache数据.

freepools integer:给log segment指定free list。RAC环境下integer为实例的个数.单实例环境下为1

index lobindexname (tablespace tablesapce_name ((storage.....):给lob列指定索引存储参数

lob创建的例子:

SQL> create tablespace person_lob datafile '+data' size 10m;

Tablespace created.

SQL> create table person_new(id number(5),name varchar2(30),remark clob,photo blob not null)
     lob (remark) store as person_remark(
     tablespace person_lob
     enable storage in row
     chunk 8192
     pctversion 2
     cache reads
     index person_remark_idx)
     lob (photo) store as person_photo(
     tablespace person_lob
     disable storage in row
     chunk 16384
     pctversion 2
     cache reads
     index person_photo_idx)
     tablespace users
     pctfree 10;

Table created.

SQL>

创建实验环境:
--创建表空间test、test_ind、test_tmp、test_blob并建立测试用户test,
SQL> create tablespace test datafile '+DATA' size 20m;

Tablespace created.

SQL> create tablespace test_ind datafile '+DATA' size 20m;

Tablespace created.

SQL> create temporary tablespace test_tmp TEMPFILE '+DATA' size 20m;

Tablespace created.

SQL> create tablespace test_blob datafile '+DATA' size 20m;

Tablespace created.

SQL> create user test identified by test default tablespace test temporary tablespace test_tmp;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

SQL> create directory EXPDP as '/home/oracle';

Directory created.

SQL> grant read,write on directory EXPDP to system;

Grant succeeded.

--给表空间test_ind、test_blob、test 扩容
SQL> alter tablespace test_ind add datafile '+DATA' size 400M;

Tablespace altered.

SQL>  alter tablespace test_blob add datafile '+DATA' size 400M;

Tablespace altered.

SQL>  alter tablespace test add datafile '+DATA' size 400M;

Tablespace altered.

--在test下创建含lob字段的表t1:
SQL> conn test/test
Connected.
SQL>  
SQL> create table T1(id number(8),name varchar2(10),addr blob,res clob,photo bfile)
     lob (addr) store as testblob
      ( tablespace test_blob
        chunk 16k
        disable storage in row
      )
     lob (res) store as testclob
      ( tablespace test_blob
        chunk 16k
        disable storage in row
       );

Table created.

SQL> show user;
USER is "TEST"
SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T1                             TEST

SQL>

表是创建在TEST表空间中。

---加载数据：
SQL> Insert Into T1 Values(1,'Gene',empty_blob(),empty_clob(),bfilename('EXPDP','IMG_0210.JPG'));

SQL> commit;

--查询：  

SQL> set lines 200
SQL> col COLUMN_NAME for a20
SQL> select table_name,column_name,segment_name,tablespace_name,index_name,chunk from user_lobs;

TABLE_NAME                     COLUMN_NAME          SEGMENT_NAME                   TABLESPACE_NAME                INDEX_NAME                          CHUNK------------------------------ -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
T1                             ADDR                 TESTBLOB                       TEST_BLOB                      SYS_IL0000088708C00003$$            16384
T1                             RES                  TESTCLOB                       TEST_BLOB                      SYS_IL0000088708C00004$$            16384

可以看到 TESTBLOB、TESTCLOB 这两个SEGMENT名称是在创建表的时候定义的

SQL> conn / as sysdba
Connected.

SQL> col SEGMENT_NAME for a25
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where OWNER='TEST';

OWNER                          SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------- ------------------ ------------------------------
TEST                           T1                        TABLE              TEST
TEST                           TESTCLOB                  LOBSEGMENT         TEST_BLOB
TEST                           TESTBLOB                  LOBSEGMENT         TEST_BLOB
TEST                           SYS_IL0000088708C00004$$  LOBINDEX           TEST_BLOB
TEST                           SYS_IL0000088708C00003$$  LOBINDEX           TEST_BLOB
 

从这里可以看到 TESTCLOB、TESTBLOB都是LOBSEGMENT类型的segment，SYS_IL0000087540C00004$$和SYS_IL0000087540C00003$$ 是LOBINDEX类型的 segment对象，且是自动创建的。

Lob大对象操作、管理

--创建有大对象字段的一张表

SQL> !export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

SQL> grant read,write on directory EXPDP to test;

Grant succeeded.

SQL> conn test/test
Connected.
SQL>

SQL> create table test001 (fname varchar2(50),content blob);

Table created.

SQL> create table test002 (fname varchar2(50),content clob);

Table created.

--(一)..准备插入大对象

--1. 创建文件存放目录（让Oracle管理，该目录）

前面已经创建好了。。。。。。。。。。。。

--2.可以将该目录授权给其他用户访问

grant read,write on directory EXPDP to scott;

--(二).准备将大对象，存放在test001表中

declare
  tempimg blob;
  tempdir bfile := bfilename('EXPDP', 'IMG_0210.jpg');
begin
  insert into test001
  values
    ('IMG_0210.jpg', empty_blob())
  returning content into tempimg;
  dbms_lob.fileopen(tempdir);
  dbms_lob.loadfromfile(tempimg, tempdir, dbms_lob.getlength(tempdir));
  dbms_lob.fileclose(tempdir);
  dbms_output.put_line('恭喜你，终于成功了！！！');
  commit;
end;
/

PL/SQL procedure successfully completed.

--将Blob对象，写成磁盘文件

declare
  l_file     utl_file.file_type;
  l_buffer   raw(32767);
  l_amount   binary_integer := 3276;
  l_pos      int := 1;
  l_blob     blob;
  l_blob_len int;

begin
  select content into l_blob from test001;
  l_blob_len := dbms_lob.getlength(l_blob);
  l_file     := utl_file.fopen('EXPDP', 'HHAHAHAHAHAHAHAHAAHA.JPG', 'wb');
  while l_pos dbms_lob.read(l_blob, l_amount, l_pos, l_buffer) ;
   utl_file.put_raw(l_file, l_buffer, true) ; l_pos :=
   l_pos + l_amount ; end loop
    ;
    utl_file.fclose(l_file);
    dbms_output.put_line('恭喜你，终于成功了！！！');
  end;
  /

PL/SQL procedure successfully completed.

实际测试的时候 HHAHAHAHAHAHAHAHAAHA.JPG的大小 跟 IMG_0210.jpg一致，可以打开。

/*文本大对象的写入和读取（clob）*/

--写入文本文件第一种方式

declare
  tempimg     clob;
  tempdir     bfile := bfilename('EXPDP', '70093.txt');
  amount      int := dbms_lob.getlength(tempdir);
  src_offset  int := 1;
  dest_offset int := 1;
  csid        int := 0;
  lc          int := 0;
  warning     int;

begin
  insert into test002
  values
    ('FIRST', empty_clob())
  returning content into tempimg;
  dbms_lob.fileopen(tempdir);
  dbms_lob.loadclobfromfile(tempimg,
                            tempdir,
                            amount,
                            dest_offset,
                            src_offset,
                            csid,
                            lc,
                            warning);
  dbms_lob.fileclose(tempdir);
  dbms_output.put_line('恭喜你终于成功了');
  commit;
end;
/

PL/SQL procedure successfully completed.

--写入文本文件第二种方式（通过异常判断文件结束的）

declare
  filecontent clob;
  input_file  utl_file.file_type;
  buffer      varchar2(2000);
  l_pos       int := 1;
  amount      int;

begin
  insert into test002
  values
    ('SECOND', empty_clob())
  returning content into filecontent;
  input_file := utl_file.fopen('EXPDP', '2.txt', 'r');
  loop
    utl_file.get_line(input_file, buffer);
    amount := length(buffer);
    exit when amount <= 0;
    dbms_lob.write(filecontent, amount, l_pos, buffer);
    l_pos := l_pos + amount;
  end loop;
  utl_file.fclose(input_file);
  dbms_output.put_line('文件写入完毕');
exception
  when no_data_found then
    dbms_output.put_line('恭喜你终于成功了');
    utl_file.fclose(input_file);
end;
/

PL/SQL procedure successfully completed.

--读取表中的数据，到文件

declare
  src     clob;
  outfile utl_file.file_type;
  length  integer;
  buffer  varchar2(8000);

begin
  select content into src from test002 where fname = 'SECOND';
  length := dbms_lob.getlength(src);
  dbms_lob.read(src, length, 1, buffer);
  outfile := utl_file.fopen('EXPDP', 'hahahahhahahahah.txt', 'w', 8000);
  utl_file.put(outfile, buffer);
  utl_file.fclose(outfile);
  dbms_output.put_line('写入完毕');
end;
/

PL/SQL procedure successfully completed.

清理CLOB字段及压缩CLOB空间

1、创建LOB字段存放表空间：

create tablespace lob_test datafile '/oracle/data/lob_test.dbf' size 500m autoextend on next 10m maxsize unlimited;

2、移动LOB字段到单独存放表空间：

ALTER TABLE CENTER_ADMIN.NWS_NEWS

MOVE LOB(ABSTRACT)

STORE AS (TABLESPACE lob_test);

说明：ABSTRACT---为一CLOB类型的字段lob_test---为新创建的表空间。

3、清空指定时间段CLOB字段的内容：

update  CENTER_ADMIN.NWS_NEWS

set ABSTRACT=EMPTY_CLOB()

where substr(to_char(pubdate,'yyyy-mm-dd'),1,4)='2011'

4、单独shrink CLOB字段：

ALTER TABLE CENTER_ADMIN.NWS_NEWS MODIFY LOB (ABSTRACT) (SHRINK SPACE);

--注：此方法会在表空间级释放出部分空间给其他对象使用，但这部分空间在操作系统级还是被占用

5、在操作系统级释放空间 （这一步 一般不做）：

alter database datafile '/oracle/data/lob_test.dbf' resize 400m

--注：绝大多数情况下，不可能一个表空间中只存放一个CLOB字段，若需要从操作系统级真正释放空间，尚需要shink table或EXP/IMP等操作。

带LOB字段表的移动

对含blob字段表迁移：

SQL> select 'alter table '||t.table_name ||' move tablespace tabespace_name lob('||t.COLUMN_NAME||') store as (tablespace tablespace_name);' from user_lobs t;

--将t1表的lob字段迁移到test_ind表空间：如下

SQL> alter table T1 move tablespace test lob(ADDR) store as (tablespace test_ind);

Table altered.

SQL> alter table T1 move tablespace test lob(RES) store as (tablespace test_ind);

Table altered.

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where OWNER='TEST';

可以看到同一个lob字段的 LOBSEGMENT和LOBINDEX类型的segment同时移动到了TEST_IND表空间,且索引处于有效状态。

--带LOB字端表的导入导出

create directory EXPDP as '/home/oracle';

grant read,write on directory EXPDP to system;

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

--上述前面已经做完，直接进行导入导出，如下；

 [oracle@dbrac1 ~]$ expdp system/oracle directory=EXPDP tables=test.t1 dumpfile=20171129exp.dmp logfile=20171129exp.log

--接着进行导入

导入表test到scott用户下，user表空间中，lob字段保存到test_blob中。
先检查：
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;

--再进行导入操作
[oracle@dbrac1 ~]$ impdp system/oracle directory=EXPDP tables=test.t1 remap_schema=test:scott remap_tablespace=test:users,TEST_IND:TEST_BLOB dumpfile=20171129exp.dmp logfile=20171129imp.log

Import: Release 11.2.0.4.0 - Production on Wed Oct 18 12:39:53 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  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
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/******** directory=EXPDP tables=test.t1 remap_schema=test:scott remap_tablespace=test:users,TEST_IND:TEST_BLOB dumpfile=20171129exp.dmp logfile=20171129imp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T1"                                6.757 KB       3 rows
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 18 12:40:09 2017 elapsed 0 00:00:12

[oracle@dbrac1 ~]$

--验证: 
 

SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where OWNER='SCOTT';

OWNER                          SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------- ------------------ ------------------------------
SCOTT                          PK_EMP                    INDEX              USERS
SCOTT                          PK_DEPT                   INDEX              USERS
SCOTT                          T4                        TABLE PARTITION    USERS
SCOTT                          T4                        TABLE PARTITION    USERS
SCOTT                          T1                        TABLE              USERS
SCOTT                          SALGRADE                  TABLE              USERS
SCOTT                          EMP                       TABLE              USERS
SCOTT                          DEPT                      TABLE              USERS
SCOTT                          TESTCLOB                  LOBSEGMENT         TEST_BLOB
SCOTT                          TESTBLOB                  LOBSEGMENT         TEST_BLOB
SCOTT                          SYS_IL0000088773C00004$$  LOBINDEX           TEST_BLOB

OWNER                          SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------- ------------------ ------------------------------
SCOTT                          SYS_IL0000088773C00003$$  LOBINDEX           TEST_BLOB

12 rows selected.

数据已经导入到 user用户下，原来lob的字段导入到test_blob表空间中

LOB性能问题

lob字段默认生成lobindex和lobsegment,在不指定特定表空间情况下，lob字段索引存储到系统表空间下。

当lob存放在表中的时候，它可以被缓存，对于它的操作效率远远高于存储在lobsegment中的lob（不用lobindex）。

当lob存放logsegment中，缺省不在缓冲区缓存，对于lob的读写都是物理IO，代价非常高，所以对于大于4kb的lob字段千万不要频繁更新，效率非常低。

当lob存放logsegment中，可定义指定使用cache（默认是nocache），这对于中等大小的lob（比如几k～几十k）很有效果，减少物理IO。

到此，相信大家对“LOB类型有哪些”有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！