合 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到底能存多大的数据呢?
1 2 3 4 5 6 7 8 9 10 | 来自于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;