> lob info #### 查看LOB相关信息 ##### 检查lob基本信息 ``` sql --查看某表空间LOB信息 set lines 160 col owner for a15 col table_name for a20 col column_name for a40 col segment_name for a25 col index_name for a25 select owner,table_name,column_name,segment_name,index_name from dba_lobs where tablespace_name='TEST'; --查看某用户lob undo信息 col owner for a15 col segment_name for a30 col column_name for a10 col tablespace_name for a15 select s.owner as owner,b.table_name as table_name,s.segment_name,s.tablespace_name, s.bytes/1024/1024/1024 as gb ,b.COLUMN_NAME,b.RETENTION,b.PCTVERSION from dba_segments s,dba_lobs b where s.segment_name=b.segment_name and s.segment_name in (select segment_name from dba_lobs where owner in ('') and s.bytes/1024/1024/1024>0.5 order by gb; --查看分区LOB信息 set lines 160 col table_name for a15 col table_owner for a15 col column_name for a15 col lob_name for a20 col lob_partition_name for a20 col tablespace_name for a15 col partition_name for a20 select table_name,table_owner,column_name,lob_name,lob_partition_name,tablespace_name,partition_name from dba_lob_partitions where table_name='TEST_PART3'; ``` ##### 查看LOB表大小 ``` sql ACCEPT SCHEMA PROMPT 'Table Owner: ' ACCEPT TABNAME PROMPT 'Table Name: ' SELECT (SELECT SUM(S.BYTES/1024/1024) FROM DBA_SEGMENTS S WHERE S.OWNER = UPPER('&SCHEMA') AND (S.SEGMENT_NAME = UPPER('&TABNAME'))) + (SELECT nvl(SUM(S.BYTES/1024/1024),0) FROM DBA_SEGMENTS S, DBA_LOBS L WHERE S.OWNER = UPPER('&SCHEMA') AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND L.TABLE_NAME = UPPER('&TABNAME') AND L.OWNER = UPPER('&SCHEMA'))) + (SELECT nvl(SUM(S.BYTES/1024/1024),0) FROM DBA_SEGMENTS S, DBA_INDEXES I WHERE S.OWNER = UPPER('&SCHEMA') AND (I.INDEX_NAME = S.SEGMENT_NAME AND I.TABLE_NAME = UPPER('&TABNAME') AND INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('&SCHEMA'))) "TOTAL TABLE SIZE(MB)" FROM DUAL; ``` #### LOB 管理 ``` sql --建表语句 create table test01.tlob1 (a number,b clob,c clob) tablespace test; Create table test01.tlob ( A number, B clob ) LOB(b) STORE AS tlob_b ( TABLESPACE test02 INDEX tlob_b_inx ( TABLESPACE test02 ) ) TABLESPACE users; --移动LOB字段 alter table test01.tlob1 move lob(c) store as (tablespace tlob); --移动LOB字段+表 注意：移动LOB会自动移动LOB字段的索引 alter table test01.TLOB1 move tablespace tlob lob(c) store as lebsegname(tablespace tlob); --修改lob pctversion alter table EXAM_ADMIN2009.EXAM_KSZP_HISTORY modify lob(ZP) (PCTVERSION 50); alter table EXAM_ADMIN2009.EXAM_KSZP_HISTORY modify lob(ZP) (PCTVERSION 10); ``` #### SecureFiles LOB ``` sql create table test_lob_securefile (t_id number,name varchar2(30),t_file clob) lob (t_file) store as securefile; --创建SecureFiles lob, 并具有透明加密功能 create table test_lob (id number,doc clob) lob (doc) store as securefile (encrypt); --查询log类型，如下 查询某个表空间下的lob信息 select segment_name,segment_type,segment_subtye from dba_segments where tablespace_name='' and segment_type= 'LOBSEGMENT'; --另外可通过 DBMS_LOB.GETOPTIONS 查询SecureFiles类型的lob字段选项，如是否去重、加密等；DBMS_LOB.SETOPTIONS进行相关设置;DBMS_SPACE.SPACE_USAGE查询SecureFiles类型的LOB字段空间使用情况 --参考：mos 1490228.1/268476.1/66431.1 ```