ORACLE LOB大字段介绍和管理

查询 lob 字段对应的表：

1 2 3 4 5 6

select e.owner, l.table_name, l.segment_name   from dba_extents e, dba_lobs l  where e.owner = l.owner and  e.segment_name = l.segment_name    and e.segment_type = 'LOBSEGMENT'    and l.segment_name like 'SYS_LOB0021730912C00004$$';

LOB类型：

将信息文件（十进制、二进制）、图像甚至音频信息采用数据库作为保存载体时，就需要使用lob类型数据。

有两种Lob，Internal Lob和External Lob。Internal Lob是指Lob数据存储在Oracle数据文件里，External Lob是指Lob数据存储在数据库外部的操作系统中。

CLOB:        存储大量、单字节、字符数据，存储在内部表空间，用于存储 字符串类型的Lob，如文本和XML文件等，字符串已数据库字符集编码。

NLOB:        存储定宽、多字节、字符数据，多字节 国家字符数据，存储在内部表空间。

BLOB:        存储较大无结构的 二进制数据，存储在内部表空间。

BFILE:        将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。数据库存储一个执行外部文件的指针，所以它是只读的。

Internal Lob和External Lob的区别

Internal Lob包含CLOB、NLOB和BLOB；External Lob只有BFILE。

Internal LOB可以作为表的一个列保存在表中，external LOB保存在操作系统上的文件中。

Internal LOB将数据以字节流的形式存储在数据库的内部。Internal LOB的许多操作都可以参与事务，可以像处理普通数据一样对其进行备份和恢复操作。

External Lob，即BFILE类型。在数据库内，该类型仅存储数据在操作系统中的位置信息，而数据的实体以外部文件的形式存在于操作系统的文件系统中。因而，该类型所表示的数据是只读的，不参与事务。

清理CLOB字段及压缩CLOB空间

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

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

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

1 2 3 4 5

ALTER TABLE CENTER_ADMIN.NWS_NEWS MOVE LOB(ABSTRACT) STORE AS (TABLESPACE lob_test); ABSTRACT---为一CLOB类型的字段 lob_test---为新创建的表空间。

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

1 2 3

update CENTER_ADMIN.NWS_NEWS set ABSTRACT=EMPTY_CLOB() where substr(to_char(pubdate,'yyyy-mm-dd'),1,4)='2011'

4、单独shrink CLOB字段：

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

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

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

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

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

带LOB字段表的移动

对含blob字段表迁移：

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

   

1 2	alter table T1 move tablespace test lob(ADDR) store as (tablespace test_ind); alter table T1 move tablespace test lob(RES) store as (tablespace test_ind);

   

1 2 3 4

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.

1 2 3 4 5 6 7 8 9

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_IND TEST                         TESTBLOB                                                                                                   LOBSEGMENT         TEST_IND TEST                         SYS_IL0000087540C00003$$                                                                      LOBINDEX              TEST_IND TEST                         SYS_IL0000087540C00004$$                                                                      LOBINDEX              TEST_IND

   

可以看到同一个lob字段的 LOBSEGMENT和LOBINDEX类型的segment同时移动到了TEST_IND表空间。

带LOB字端表的导入导出

导出

1 2 3

create directory EXPDP as '/backup/expdp'; grant read,write on directory EXPDP to system; export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

1 2 3 4 5 6

[oracle@primary ~]$ expdp system/oracle directory=EXPDP tables=test.t1 dumpfile=20160114expt1.dmp logfile=20160114expt1.log 导入 导入表test到scott用户下，user表空间中，lob字段保存到test_blob中。   alter user scott identified by tiger account unlock; export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK [oracle@primary ~]$ impdp system/oracle directory=EXPDP tables=test.t1 remap_schema=test:scott remap_tablespace=test:users,TEST_IND:TEST_BLOB dumpfile=20160114expt1.dmp logfile=imp20160114expt1.log

更改分区表的lob的表空间

1 2 3 4 5 6 7 8 9 10 11 12

create table show_lob_storage (aaa number(5),  bbb varchar2(10),  ccc number(5),  ddd CLOB ) PARTITION BY RANGE(aaa) (PARTITION p1 VALUES LESS THAN (50) tablespace AAA    LOB (ddd) STORE AS (tablespace partforlob01),  PARTITION p2 VALUES LESS THAN (100) tablespace BBB    LOB (ddd) STORE AS (tablespace partforlob02),  PARTITION p3 VALUES LESS THAN (MAXVALUE) tablespace CCC    LOB (ddd) STORE AS (tablespace partforlob03));

1 2 3 4 5 6 7

SQL> select partition_name,lob_partition_name,tablespace_name  from user_lob_partitions where table_name = 'SHOW_LOB_STORAGE';    PARTITION_NAME                 LOB_PARTITION_NAME             TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ P1                             SYS_LOB_P70                    PARTFORLOB01 P2                             SYS_LOB_P76                    PARTFORLOB02 P3                             SYS_LOB_P72                    PARTFORLOB03

1	SQL> alter table SHOW_LOB_STORAGE move partition P2 lob(DDD) store as (tablespace PARTFORLOB03);

 

1 2 3 4 5 6 7

SQL> select partition_name,lob_partition_name,tablespace_name  from user_lob_partitions where table_name = 'SHOW_LOB_STORAGE';    PARTITION_NAME                 LOB_PARTITION_NAME             TABLESPACE_NAME ------------------------------ ------------------------------ ------------------------------ P1                             SYS_LOB_P70                    PARTFORLOB01 P2                             SYS_LOB_P78                    PARTFORLOB03 P3                             SYS_LOB_P72                    PARTFORLOB03