lob info

查看LOB相关信息

检查lob基本信息

--查看某表空间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表大小

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 管理

--建表语句
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

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