> 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 
```

【LOB】Oracle Lob管理常用sql

相關文章