關(guān)于Oracle數(shù)據(jù)庫(kù)LOB大字段總結(jié)
在ORACLE數(shù)據(jù)庫(kù)中，DBA_OBJECTS視圖中OBJECT_TYPE為L(zhǎng)OB的對(duì)象是什么東西呢?其實(shí)OBJECT_TYPE為L(zhǎng)OB就是大對(duì)象(LOB)，它指那些用來存儲(chǔ)大量數(shù)據(jù)的數(shù)據(jù)庫(kù)字段。
Oracle 11gR2 文檔：
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e18294/adlob_tables.htm#ADLOB45267
一、LOB 分類
LOB大對(duì)象主要是用來存儲(chǔ)大量數(shù)據(jù)的數(shù)據(jù)庫(kù)字段，在Oracle 9iR2 中LOB的最大容量是4G，Oracle 10g 最大8T，Oracle 11g 最大是128T。具體取決于blocksize 的大小。
| The built-in LOB data types BLOB, CLOB, and NCLOB (stored internally) and BFILE (stored externally) can store large and unstructured data such as text, image, video, and spatial data. The size of BLOB, CLOB, and NCLOB data can be up to (232-1 bytes) * (the value of the CHUNK parameter of LOB storage). |
1. Oracle 支持4 種類型的LOB：
- CLOB：字符LOB。這種類型用于存儲(chǔ)大量的文本信息，如XML 或者只是純文本。這個(gè)數(shù)據(jù)類型需要進(jìn)行字符集轉(zhuǎn)換，也就是說，在獲取時(shí)，這個(gè)字段中的字符會(huì)從數(shù)據(jù)庫(kù)的字符集轉(zhuǎn)換為客戶的字符集，而在修改時(shí)會(huì)從客戶的字符集轉(zhuǎn)換為數(shù)據(jù)庫(kù)的字符集。
- NCLOB：這是另一種類型的字符LOB。存儲(chǔ)在這一列中的數(shù)據(jù)所采用的字符集是數(shù)據(jù)庫(kù)的國(guó)家字符集，而不是數(shù)據(jù)庫(kù)的默認(rèn)字符集。
- BLOB：二進(jìn)制LOB。這種類型用于存儲(chǔ)大量的二進(jìn)制信息，如字處理文檔，圖像和你能想像到的任何其他數(shù)據(jù)。它不會(huì)執(zhí)行字符集轉(zhuǎn)換。應(yīng)用向BLOB 中寫入什么位和字節(jié)，BLOB就會(huì)返回什么為和字節(jié)。
- BFILE：二進(jìn)制文件LOB。這與其說是一個(gè)數(shù)據(jù)庫(kù)存儲(chǔ)實(shí)體，不如說是一個(gè)指針。帶BFILE列的數(shù)據(jù)庫(kù)中存儲(chǔ)的只是操作系統(tǒng)中某個(gè)文件的一個(gè)指針。這個(gè)文件在數(shù)據(jù)庫(kù)之外維護(hù)，根本不是數(shù)據(jù)庫(kù)的一部分。BFILE 提供了文件內(nèi)容的只讀訪問。
2. LOB數(shù)據(jù)類型分類
(1) 按存儲(chǔ)數(shù)據(jù)的類型分：
字符類型：
- CLOB：存儲(chǔ)大量 單字節(jié) 字符數(shù)據(jù)。
- NLOB：存儲(chǔ)定寬 多字節(jié) 字符數(shù)據(jù)。
二進(jìn)制類型：
- BLOB:存儲(chǔ)較大無結(jié)構(gòu)的二進(jìn)制數(shù)據(jù)。
二進(jìn)制文件類型：
- BFILE：將二進(jìn)制文件存儲(chǔ)在數(shù)據(jù)庫(kù)外部的操作系統(tǒng)文件中。存放文件路徑。
(2) 按存儲(chǔ)方式分：
- 存儲(chǔ)在內(nèi)部表空間(內(nèi)部LOB)：CLOB，NLOB和BLOB
- 指向外部操作系統(tǒng)文件(外部LOB)：BFILE
二、Lob的存儲(chǔ)
我們建立含有l(wèi)ob字段的表時(shí)，oracle會(huì)自動(dòng)為lob字段建立兩個(gè)單獨(dú)的segment,一個(gè)用來存放數(shù)據(jù)，另一個(gè)用來存放索引，并且它們都會(huì)存儲(chǔ)在對(duì)應(yīng)表指定的表空間中。
如上例所示，每個(gè)lob字段都對(duì)應(yīng)兩個(gè)segment，其中存放lob數(shù)據(jù)的以SYS_LOB開頭，存放索引以SYS_IL開頭。
LOB 按“塊”(chunk)或(piece)來存儲(chǔ)，每個(gè)片段都可以訪問。
三、Lob與其它類型的轉(zhuǎn)換
通過TO_CLOB可以將CHAR，NCHAR，VARCHAR2,NVARCHAR2，NCLOB類型轉(zhuǎn)換成CLOB;
通過TO_LOB可以將LONG RAW轉(zhuǎn)換成BLOB，LONG轉(zhuǎn)換成CLOB;
通過TO_NCLOB可以將CHAR，NCHAR，VARCHAR2,NVARCHAR2，CLOB轉(zhuǎn)換成NCLOB。
四、Oracle數(shù)據(jù)庫(kù)的SYS_LOB
看看你的表里是不是存在blog，clob等類型的字段，當(dāng)我們所建立的表中含有l(wèi)ob型的數(shù)據(jù)時(shí)，oracle會(huì)為每個(gè)lob字段生成一個(gè)獨(dú)立的segment用來存放數(shù)據(jù)，同時(shí)也建立了獨(dú)立的index segment .oracle對(duì)它們是單獨(dú)管理的。
普通表只會(huì)新增一個(gè)或兩個(gè)段對(duì)象.類型為TABLE和INDEX，數(shù)據(jù)就存放在表段中.索引就放在索引段中。但是LOB列則額外新增了兩個(gè)段對(duì)象,類型為L(zhǎng)OBSEGMENT和LOBINDEX，LOBINDEX用于指向LOB段,找出其中的某一部分，所以存儲(chǔ)在表中的LOB存儲(chǔ)的是一個(gè)地址,或者說是一個(gè)指針,實(shí)際上表中的lob列中存的是一個(gè)地址段.然后在lobindex找到所有的地址段.然后在lobSegment中把所有地址段的值都讀取了來。所以lobSegment就保存了LOG列的真正的數(shù)據(jù)，所以會(huì)非常大，并且獨(dú)立于原始表存在。
先看看這個(gè)對(duì)應(yīng)的表的字段是否有數(shù)據(jù)，如果有你就無法刪除這個(gè)sys_lob$的對(duì)象。想減少空間的占用就清理歷史數(shù)據(jù)，或者重新導(dǎo)出導(dǎo)入下。
五、相關(guān)概念
關(guān)于LOB，我們可以使用dbms_metadata來獲得它的完整的腳本：
- SELECT DBMS_METADATA.GET_DDL( 'TABLE', 'LOB_TABLE' ) FROM DUAL
1. 表空間
保存lob數(shù)據(jù)的表空間可以不同于保存表數(shù)據(jù)的表空間，為L(zhǎng)OB數(shù)據(jù)單獨(dú)使用一個(gè)表空間有利于備份和恢復(fù)以及空間管理但是lobindex和lobsegment必須在同一個(gè)表空間中
2. IN ROW
- ENABLE STORAGE IN ROW
- DISABLE STORAGE IN ROW
控制LOB數(shù)據(jù)是否總與表分開存儲(chǔ)(存儲(chǔ)在lobsegment中)，或是有時(shí)可以與表一同存儲(chǔ)，而不用單獨(dú)放在lobsegment中。
如果設(shè)置了ENABLE STORAGE IN ROW，而不是DISABLE STORAGE IN ROW，小LOB(最多4,000字節(jié))就會(huì)像VARCHAR2一樣存儲(chǔ)在表本身中。只有當(dāng)LOB超過了4,000字節(jié)時(shí)，才會(huì)“移出”到lobsegment中
默認(rèn)行為是啟用行內(nèi)存儲(chǔ)ENABLE STORAGE IN ROW，如果lob存儲(chǔ)的數(shù)據(jù)大小能在表本身中放下，建議采用內(nèi)聯(lián)存儲(chǔ)
3. CHUNK
塊(chunk)是邏輯上連續(xù)的一組數(shù)據(jù)庫(kù)塊(block)，這也是LOB的最小分配單元。，每個(gè)LOB實(shí)例(每個(gè)行外存儲(chǔ)的LOB值)會(huì)占用至少一個(gè)CHUNK。一個(gè)CHUNK有一個(gè)LOB值使用，每個(gè)chunk的大小應(yīng)該盡可能與實(shí)際lob數(shù)據(jù)的大小相近，以減少浪費(fèi)空間;
4. PCTVERSION
控制lob的讀一致性。
PCTVERSION控制著用于實(shí)現(xiàn)LOB數(shù)據(jù)版本化的已分配LOB空間的百分比(這些數(shù)據(jù)庫(kù)塊由某個(gè)時(shí)間點(diǎn)的LOB所用，并處在lobsegment的HWM以下)。對(duì)于許多使用情況來說，默認(rèn)設(shè)置12%就足夠了，因?yàn)樵诤芏嗲闆r下，你只是要INSERT和獲取LOB(通常不會(huì)執(zhí)行LOB的更新;LOB往往會(huì)插入一次，而獲取多次)。因此，不必為L(zhǎng)OB版本化預(yù)留太多的空間(甚至可以沒有)。
如果你的應(yīng)用確實(shí)經(jīng)常修改LOB，假設(shè)很頻繁地讀LOB，與此同時(shí)另外某個(gè)會(huì)話正在修改這些LOB，12%可能就太小了。如果處理LOB時(shí)遇到一個(gè)ORA-22924錯(cuò)誤，解決方案不是增加undo表空間的大小，也不是增加undo保留時(shí)間(UNDO_RETENTION)，如果你在使用手動(dòng)undo管理，那么增加更多RBS空間也不能解決這個(gè)問題。而是應(yīng)該使用以下命令：
- ALTER TABLE tabname MODIFY LOB (lobname) ( PCTVERSION n)
增加lobsegment中為實(shí)現(xiàn)數(shù)據(jù)版本化所用的空間大小。
5. CACHE
控制lobsegment數(shù)據(jù)是否存儲(chǔ)在緩沖區(qū)緩存中。默認(rèn)的NOCACHE指示，每個(gè)訪問都是從磁盤的一個(gè)直接讀
- ALTER TABLE tabname MODIFY LOB (lobname) ( CACHE );
- ALTER TABLE tabname MODIFY LOB (lobname) ( NOCACHE );
六、查看ORACLE的LOB(BLOB和CLOB)對(duì)象占用的大小
1. 查看Oracle中表空間及表數(shù)據(jù)大小
- Select Segment_Name, Sum(bytes) / 1024 / 1024
- From User_Extents
- where SEGMENT_NAME LIKE 'SYS_LOB%'
- GROUP BY Segment_Name
- order by Sum(bytes) / 1024 / 1024 desc;
從返回的結(jié)果看，有一個(gè)segment名為"SYS_LOB0000701017C00045$$"的對(duì)象占用了大量的空間，這種帶有SYS_LOB***即LOB(BLOB和CLOB)對(duì)象占用數(shù)據(jù)庫(kù)的空間名稱。
2. 根據(jù)segment_name，就可以從 dba_lobs 表里查到是哪個(gè)表，哪個(gè)字段
- SELECT * FROM DBA_LOBS WHERE SEGMENT_NAME LIKE 'SYS_LOB0000701017C00045$$';
