oracle lob 簡單介紹

何為LOB？

lob為oracle資料庫的一個大物件資料型別,可以儲存超過4000bytes的字串，二進位制資料，OS檔案等大物件資訊.最大可儲存的容量根oracle的版本和oracle 塊大小有關.

有那幾種可供選擇的LOB型別?

目前ORACLE提供了CLOB，NCLOB，BLOB，BFILE共四種LOB型別,CLOB,NLOB為大字串型別,NLOB為多語言集字元型別,類似於NVARCHAR型別,用他們代替以前的LONG型別;BLOG為大二進位制型別,用來代替以前的LONG RAW型別;BFILE可儲存作業系統中的各種檔案.

何為Internal LOBs 、External LOBs？

Internal LOBs指的是LOB資料儲存在oracle的tablespace中,CLOB，NCLOB，BLOG都是Internal LOBs; 而External LOBs指的是LOB資料儲存在資料庫外部的作業系統檔案中,BFIEL是唯一的External LOBs.BFILE提供了讓我們可以在SQL中訪問外部檔案的方法.

LOB到底能存多大的資料呢?

來自於10G Oracle Database reference BFILE ：Maximum size: 4 GB  Maximum size of a file name: 255 characters  Maximum size of a directory name: 30
          characters Maximum number of open BFILEs: The maximum number of BFILEs is limited by the value of the
          SESSION_MAX_OPEN_FILES initialization parameter, which is itself limited by the maximum number of open 
          files the operating system will allow.
BLOB： Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)  The number of LOB columns
         per table is limited only by the maximum number of columns per table (that is,10001).
CLOB： Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns
         per table is limited onlyby the maximum number of columns per table (that is,10001).

NCLOB ：Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB) The number of LOB columns
  per table is limited only by the maximum number of columns per table (that is,10001).

LOB可以用在什麼地方?

LOB除了可以用在TABLE中定義列的型別外,還可以用來儲存XML資料,ORACLE的集合型別,看下面的例子:

1) create table lobtest(a varchar2(20), b clob, c blob) tablespace users; –lob表的定義

2) LOB儲存集合型別

create tablespace person_lob datafile size 102464K autoextend on next 100M maxsize 1024000K extent management local uniform size 1M segment space management auto;
/
alter user mayp quota unlimited on person_lob;

create or replace type person_att is object(id number(5),name varchar2(30),sex varchar2(1),age number(5,2));
/
create or replace type person_atts is varray(5) of person_att;
/
create table person(person_id number(5),attribute1 person_atts)
varray attribute1 store as lob person_attribute(
tablespace person_lob
enable storage in row
chunk 16384
pctversion 5
cache reads
index person_lob_idx)
tablespace users
pctfree 0
/
上面定義了一個用lob儲存varray型別資料的例子.

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的空間浪費.看下面的表格能說明一切:

  Data Size                     CHUNK Size    Disk Space Used to Store the LOB  Space Utilization(Percent)
  3500 enable storage in row    irrelevant    3500 in row                        100 3500 disable storage in row   32 KB         32 KB                              10 3500 disable storage in row   4 KB          4 KB                               90 33 KB                         32 KB         64 KB                              51 2 GB +10                      32 KB         2 GB + 32 KB                       99+

上面表格用一些資料說明了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 n /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資料.

lob建立的例子:
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
/

LOB可以在SQL環境操作嗎?

LOB可以像使用其他資料型別一樣,可以進行DML操作.

insert into person_new values(1,’robin.ma’,'he is a goold boy’,empty_blob());

update person_new set remark=’he is a goog boy,he is from Beijing China’ where id = 1;

delete from person_new where id = 1;

commit;

create table bfile_test(files bfile) tablespace users;

insert into bfile_test values(bfilename(’EXPORT’,'c.dat’));

commit;

SQL> select * from bfile_test;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> select * from person_new;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus

SQL> select remark from person_new;

REMARK
——————————————————————————–
he is a goog boy,he is from Beijing China

從上面的例子可以檢視,BLOG和BFIEL 是不能直接在sql*plus顯示的.

可以操作LOB的API有哪些?

PL/SQL可以用DBMS_LOB操作任何的LOB，BFILE物件,Oracle還提供了OCI程式設計介面操作LOB。Java,C#都提供了相應的API操作LOB。

怎樣將其他字元型別轉換成CLOB，二進位制型別轉換成BLOB？

透過TO_CLOB可以將CHAR，NCHAR，VARCHAR2,NVARCHAR2，NCLOB型別轉換成CLOB；
透過TO_LOB可以將LONG RAW轉換成BLOB，LONG轉換成CLOB；
透過TO_NCLOB可以將CHAR，NCHAR，VARCHAR2,NVARCHAR2，CLOB轉換成NCLOB。

LOB物件可以db link遠端操作嗎?

除了BFILE,CLOB，BLOB可以區域性的訪問和操作遠端LOB物件.目前下列操作是允許的:

CREATE TABLE AS SELECT …FROM ;
INSERT INTO .. SELECT * FORM ;
UPDATE …SET .. = (SELECT ..FROM );
INSERT INTO (…) AS SELECT * FROM LOCAL_TABLE;
UPDATE  SET .. = (SELECT .. FROM local_table);
DELETE FROM  WHERE …..

LOB相關的SQL function和DBMS_LOB不允許訪問和操作遠端的LOB物件。

LOB列可以索引嗎?

目前CLOB列支援Oracle Text Index,Domain Index,Function-Base Index,Extensible Index.

LOB支援分割槽表嗎?

可以在分割槽表使用LOB列,並且還支援exchange partition，move partition,merge partition等操作.

LOB支援Index-organization table嗎?

oracle支援在index-organization table中建立clob,blog和bfile列, 但不支援在分割槽的index-organization table中建立lob列.

參考:Oracle? Database Application Developer’s Guide - Large Objects 10g Release 2 (10.2)

----------------------------------&gt>
轉載於:http://www.cnblogs.com/sopost/archive/2010/01/19/2190124.html