shrink含lob字段的表

回收表的方法有shrink/move/导入导出，以及另外还有一种方法是可以通过在新的表空间重建这张表来完成：
create table tab2 as select * from tab;
drop table tab purge;
rename tab2 to tab;
也可以达到同样的目的，不过有点麻烦。
所以如果有需要，最好选在业务空闲时间move表段或索引段，避免影响业务

--本实验是对含有lob字段的表做shrink操作。
需求对sys用户下的表lob1进行收缩（含有lob字段）:

需求对sys用户下的表lob1进行收缩（含有lob字段）:
模拟数据表lob1：
SQL>  create table lob1(line number,text clob) tablespace users;

Table created.

SQL>  insert into lob1  select line,text from all_source;

637502 rows created.

SQL> insert into lob1 select * from lob1;

637502 rows created.

................省略.................................

SQL> select count(*) from lob1;

  COUNT(*)
----------
  10200032

SQL> commit;

Commit complete.
 
分析表：
SQL> analyze table lob1 compute statistics;

Table analyzed.


1.查询对象属主
set lines 200
col OWNER for a20
col OBJECT_NAME for a30
col OBJECT_TYPE for a30
select owner,object_name,object_type from dba_objects where object_name='LOB1';


OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ ------------------------------
SYS                  LOB1                           TABLE

2.查询表大小
--查询表大小(包含表和lob字段)
select (select nvl(sum(s.bytes/1024/1204), 0)                              -- the table segment size  
          from dba_segments s
         where s.owner = upper('SYS')
           and (s.segment_name = upper('LOB1'))) +
       (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob segment size  
          from dba_segments s, dba_lobs l
         where s.owner = upper('SYS')
           and (l.segment_name = s.segment_name and
               l.table_name = upper('LOB1') and
               l.owner = upper('SYS'))) +
       (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob index size  
          from dba_segments s, dba_indexes i
         where s.owner = upper('SYS')
           and (i.index_name = s.segment_name and
               i.table_name = upper('LOB1') and index_type = 'lob' and
               i.owner = upper('SYS'))) "total_table_size_M"
  from dual;

total_table_size_M
------------------
        1747.93355
        
--查询表大小(不包含lob字段)
col SEGMENT_NAME for a30
col PARTITION_NAME for a30
select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 M from dba_segments where segment_name='LOB1' and owner='SYS';

OWNER                SEGMENT_NAME                   PARTITION_NAME                          M
-------------------- ------------------------------ ------------------------------ ----------
SYS                  LOB1                                                                1472

--查询表大小(只包含lob字段)       
set lines 200 pages 999
col owner  for a15
col TABLE_NAME for a20
col COLUMN_NAME for a30
col SEGMENT_NAME for a30
select a.owner,  
       a.table_name,  
       a.column_name,  
       b.segment_name,
       b.segment_type,  
       ROUND(b.BYTES / 1024 / 1024)  
  from dba_lobs a, dba_segments b  
 where a.segment_name = b.segment_name  
   and a.owner = 'SYS'  
   and a.table_name = 'LOB1'  
union all  
select a.owner,  
       a.table_name,  
       a.column_name,  
       b.segment_name,
       b.segment_type,  
       ROUND(b.BYTES / 1024 / 1024)  
  from dba_lobs a, dba_segments b  
 where a.index_name = b.segment_name  
   and a.owner = 'SYS'  
   and a.table_name = 'LOB1';
   

OWNER           TABLE_NAME           COLUMN_NAME                    SEGMENT_NAME                   SEGMENT_TYPE       ROUND(B.BYTES/1024/1024)
--------------- -------------------- ------------------------------ ------------------------------ ------------------ ------------------------
SYS             LOB1                 TEXT                           SYS_LOB0000089531C00002$$      LOBSEGMENT                              496
SYS             LOB1                 TEXT                           SYS_IL0000089531C00002$$       LOBINDEX                                  0

--查询表并行度：
col DEGREE for a10
select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len,DEGREE,LAST_ANALYZED,PARTITIONED,SAMPLE_SIZE from dba_tables where table_name='LOB1' and owner='SYS';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN DEGREE     LAST_ANALYZE PAR SAMPLE_SIZE
---------- ---------- ---------- ---------- ---------- ----------- ---------- ------------ --- -----------
  10200032     187748        668        908          0         125          1 12-AUG-17    NO     10200032

注意：DBMS_STATS包无法获取EMPTY_BLOCKS统计信息，所以需要用analyze命令收集统计信息
                                                                                                   NOT NULL DATE

3.查询表索引情况
col TABLESPACE_NAME for a15
col OWNER for a15
col INDEX_NAME for a30
col TABLE_OWNER for a15
col TABLE_NAME for a20
col PARTITION for a15
col DEGREE for a10
col STATUS for a10
select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,DEGREE,PARTITIONED from dba_indexes where table_name='LOB1' and table_owner='SYS';

OWNER           INDEX_NAME                     TABLE_OWNER     TABLE_NAME           TABLESPACE_NAME STATUS     LAST_ANALYZE DEGREE     PAR
--------------- ------------------------------ --------------- -------------------- --------------- ---------- ------------ ---------- ---
SYS             SYS_IL0000089534C00002$$       SYS             LOB1                 USERS           VALID                   0          NO


索引SYS_IL0000089534C00002$$是lob字段的索引。

col INDEX_OWNER for a15
col COLUMN_NAME for a15
select INDEX_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='LOB1' and table_owner='SYS';

no rows selected

4.检查
做收缩的时候归档经常会满；这次也要注意监控一下归档文件是否会占满存储空间导致无法登陆数据库了(本实验未开归档)
注意临时表空间空间使用情况

--查看临时表空间：
set lines 200
col tablespace_name for a15          
col FILE_NAME for a60
select tablespace_name,file_name,bytes/1024/1024/1024 size_G,autoextensible from dba_temp_files;

TABLESPACE_NAME FILE_NAME                                                        SIZE_G AUTOEXTEN
--------------- ------------------------------------------------------------ ---------- ---------
TEMP            +DATADG/ossdb/tempfile/temp.285.906807625                            30 NO

--临时表空间使用情况
Select f.tablespace_name,
       sum(f.bytes_free + f.bytes_used) / 1024 / 1024 / 1024 "total GB",
       sum((f.bytes_free + f.bytes_used) - nvl(p.bytes_used, 0)) / 1024 / 1024 / 1024 "Free GB",
       sum(nvl(p.bytes_used, 0)) / 1024 / 1024 / 1024 "Used GB"
  from sys.v_$temp_space_header f,
       dba_temp_files           d,
       sys.v_$temp_extent_pool  p
 where f.tablespace_name(+) = d.tablespace_name
   and f.file_id(+) = d.file_id
   and p.file_id(+) = d.file_id
 group by f.tablespace_name;

5.查看高水位
查看高水位：
col statement1 for a60
col statement2 for a60
col statement3 for a60
col gather for a70
select table_name,
       num_rows,
       avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
       blocks * 8 / 1024 high_water_mb,
       (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb,
       'alter table ' || owner || '.' || table_name || ' enable row movement;' statement1,
       'alter table ' || owner || '.' || table_name || ' shrink space cascade;' statement2,
       'alter table ' || owner || '.' || table_name || ' disable row movement;' statement3,
       'exec dbms_stats.gather_table_stats(''' || owner || ''',''' || table_name || ''');' gather
  from dba_tables
 where table_name = 'LOB1' and owner='SYS';
 
TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB STATEMENT1                                                   STATEMENT2
-------------------- ---------- ---------- ------------- ---------------- ------------------------------------------------------------ --------------------------------
STATEMENT3                                                   GATHER
------------------------------------------------------------ ----------------------------------------------------------------------
LOB1                   10200032 1351.04285    1466.78125       115.738397 alter table SYS.LOB1 enable row movement;                    alter table SYS.LOB1 shrink space cascade;
alter table SYS.LOB1 disable row movement;                   exec dbms_stats.gather_table_stats('SYS','LOB1');

模拟删除数据产生碎片：
SQL> delete from lob1 where rownum<2000000;

1999999 rows deleted.

--再次查看高水位：
 select table_name,
        num_rows,
        avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
        blocks * 8 / 1024 high_water_mb,
        (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
 from dba_tables
  where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                   10200032 1351.04285    1466.78125       115.738397

--高水位未变，因未收集统计信息：
SQL> analyze table lob1 compute statistics;

Table analyzed.

--验证高水位及碎片
 select table_name,
        num_rows,
        avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
        blocks * 8 / 1024 high_water_mb,
        (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
 from dba_tables
  where table_name = 'LOB1' and owner='SYS';
 
TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    8200033 1086.13345    1466.78125       380.647796

6.shrink 表                                                       
6.1前提：允许行移动
alter table SYS.LOB1 enable row movement;

6.2开启并行，并加nologging选项
alter table SYS.LOB1 parallel 3 nologging;

--验证
select DEGREE,logging,ROW_MOVEMENT from dba_tables where table_name='LOB1' and owner='SYS';

DEGREE     LOG ROW_MOVE
---------- --- --------
         3 NO  ENABLED

6.3开始收缩表
--先查询sid
select sid from v$mystat where rownum=1;

       SID
----------
         1

--查看并行进程：（查看并行有无起来，在正式shrink表前后检查）
select slave_name,status,sessions from v$pq_slave;

no rows selected

select s.sid,s.serial#,s.sql_id,q.sql_text from v$session s,v$sql q where s.sql_id=q.sql_id and q.sql_id in(select sql_id from v$sql where sql_text like 'alter table SYS.LOB1 shrink space cascade');


--正式收缩表
alter table SYS.LOB1 shrink space cascade;

--注意查看归档目录、有无耗尽情况
--查看并行进程：
select slave_name,status,sessions from v$pq_slave;

no rows selected

select s.sid,s.serial#,s.sql_id,q.sql_text from v$session s,v$sql q where s.sql_id=q.sql_id and q.sql_id in(select sql_id from v$sql where sql_text like 'alter table SYS.LOB1 shrink space cascade');

no rows selected

查询结果并无并行子进程在运行，即shrink操作不支持并行。

--查看进度
col OPNAME for a35
col username for a15
col SQL_ID for a15
select sid, serial#, opname, to_char(start_time, 'HH24:MI:SS') AS "START",(sofar/totalwork)*100 as percent_complete,TIME_REMAINING,ELAPSED_SECONDS,SQL_ID,USERNAME from v$session_longops;

--查看高水位：
 select table_name,
        num_rows,
        avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
        blocks * 8 / 1024 high_water_mb,
        (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
 from dba_tables
  where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    8200033 1086.13345    1466.78125       380.647796

6.4收集统计信息：
analyze table lob1 compute statistics;

Table analyzed.

--再次查看
 select table_name,
        num_rows,
        avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
        blocks * 8 / 1024 high_water_mb,
        (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
 from dba_tables
  where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    8200033 1086.13345    1132.53125       46.3977964

--查看表大小：
  select (select nvl(sum(s.bytes/1024/1204), 0)                              -- the table segment size  
           from dba_segments s
          where s.owner = upper('SYS')
            and (s.segment_name = upper('LOB1'))) +
        (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob segment size  
           from dba_segments s, dba_lobs l
          where s.owner = upper('SYS')
            and (l.segment_name = s.segment_name and
                l.table_name = upper('LOB1') and
                l.owner = upper('SYS'))) +
        (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob index size  
           from dba_segments s, dba_indexes i
          where s.owner = upper('SYS')
            and (i.index_name = s.segment_name and
                i.table_name = upper('LOB1') and index_type = 'lob' and
                i.owner = upper('SYS'))) "total_table_size_M"
   from dual;
   
total_table_size_M
------------------
        1463.44186
值减小
        

select a.owner,  
        a.table_name,  
        a.column_name,  
        b.segment_name,
        b.segment_type,  
        ROUND(b.BYTES / 1024 / 1024)  
   from dba_lobs a, dba_segments b  
  where a.segment_name = b.segment_name  
    and a.owner = 'SYS'  
    and a.table_name = 'LOB1'  
 union all  
 select a.owner,  
        a.table_name,  
        a.column_name,  
        b.segment_name,
        b.segment_type,  
        ROUND(b.BYTES / 1024 / 1024)  
   from dba_lobs a, dba_segments b  
  where a.index_name = b.segment_name  
    and a.owner = 'SYS'  
    and a.table_name = 'LOB1';
    
OWNER           TABLE_NAME           COLUMN_NAME     SEGMENT_NAME                   SEGMENT_TYPE       ROUND(B.BYTES/1024/1024)
--------------- -------------------- --------------- ------------------------------ ------------------ ------------------------
SYS             LOB1                 TEXT            SYS_LOB0000089545C00002$$      LOBSEGMENT                              496
SYS             LOB1                 TEXT            SYS_IL0000089545C00002$$       LOBINDEX                                  2
大小不变

select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 M from dba_segments where segment_name='LOB1' and owner='SYS';

OWNER           SEGMENT_NAME                   PARTITION_NAME                          M
--------------- ------------------------------ ------------------------------ ----------
SYS             LOB1                                                              1137.5
值减小
        
SQL>  select 1747.93355-1463.44186 from dual;

1747.93355-1463.44186
---------------------
            284.49169        --lob1表包含lob字段的收缩值

SQL> select 1472-1137.5 from dual;

1472-1137.5
-----------
      334.5                 --lob1表未包含lob字段的收缩值
      
验证shrink不支持并行：
delete from lob1 where rownum<2000000;

1999999 rows deleted.

--查询
select slave_name,status,sessions from v$pq_slave;

SLAV STAT   SESSIONS
---- ---- ----------
P000 BUSY          2
P001 BUSY          2
P002 BUSY          1

select s.sid,s.serial#,s.sql_id,q.sql_text from v$session s,v$sql q where s.sql_id=q.sql_id and q.sql_id in(select sql_id from v$sql where sql_text like 'delete from lob1 where rownum<2000000');

       SID    SERIAL# SQL_ID               SQL_TEXT
---------- ---------- -----------------------------------------------------------
         1          7 g5p7xf7890sxs         delete from lob1 where rownum<2000000
        30         39 g5p7xf7890sxs         delete from lob1 where rownum<2000000
        32         11 g5p7xf7890sxs         delete from lob1 where rownum<2000000
        36         51 g5p7xf7890sxs         delete from lob1 where rownum<2000000

经过查询有并行；
 
6.5关并行及nologging：
alter table SYS.LOB1 noparallel logging;

6.6关闭行移动：
alter table SYS.LOB1 disable row movement;

--验证
select DEGREE,logging,ROW_MOVEMENT from dba_tables where table_name='LOB1' and owner='SYS';

DEGREE     LOG ROW_MOVE
---------- --- --------
         1 YES DISABLED



7.再次分析表lob1
analyze table lob1 compute statistics;

--查看；
 select table_name,
        num_rows,
        avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
        blocks * 8 / 1024 high_water_mb,
        (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
 from dba_tables
  where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    6200034 821.224054    1132.53125       311.307196
 
--查看表大小
  select (select nvl(sum(s.bytes/1024/1204), 0)                              -- the table segment size  
           from dba_segments s
          where s.owner = upper('SYS')
            and (s.segment_name = upper('LOB1'))) +
        (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob segment size  
           from dba_segments s, dba_lobs l
          where s.owner = upper('SYS')
            and (l.segment_name = s.segment_name and
                l.table_name = upper('LOB1') and
                l.owner = upper('SYS'))) +
        (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob index size  
           from dba_segments s, dba_indexes i
          where s.owner = upper('SYS')
            and (i.index_name = s.segment_name and
                i.table_name = upper('LOB1') and index_type = 'lob' and
                i.owner = upper('SYS'))) "total_table_size_M"
   from dual;

total_table_size_M
------------------
        1463.44186

 select a.owner,  
        a.table_name,  
        a.column_name,  
        b.segment_name,
        b.segment_type,  
        ROUND(b.BYTES / 1024 / 1024)  
   from dba_lobs a, dba_segments b  
  where a.segment_name = b.segment_name  
    and a.owner = 'SYS'  
    and a.table_name = 'LOB1'  
 union all  
 select a.owner,  
        a.table_name,  
        a.column_name,  
        b.segment_name,
        b.segment_type,  
        ROUND(b.BYTES / 1024 / 1024)  
   from dba_lobs a, dba_segments b  
  where a.index_name = b.segment_name  
    and a.owner = 'SYS'  
    and a.table_name = 'LOB1';

OWNER           TABLE_NAME           COLUMN_NAME     SEGMENT_NAME                   SEGMENT_TYPE       ROUND(B.BYTES/1024/1024)
--------------- -------------------- --------------- ------------------------------ ------------------ ------------------------
SYS             LOB1                 TEXT            SYS_LOB0000089545C00002$$      LOBSEGMENT                              496
SYS             LOB1                 TEXT            SYS_IL0000089545C00002$$       LOBINDEX                                  3

select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 M from dba_segments where segment_name='LOB1' and owner='SYS';

OWNER           SEGMENT_NAME                   PARTITION_NAME                          M
--------------- ------------------------------ ------------------------------ ----------
SYS             LOB1                                                              1137.5

再次shrink表：
SQL> alter table SYS.LOB1 enable row movement;

Table altered.

SQL> alter table SYS.LOB1 shrink space cascade;
   
Table altered.

SQL> analyze table lob1 compute statistics;

Table analyzed.

SQL>  select table_name,
  2          num_rows,
  3          avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
  4          blocks * 8 / 1024 high_water_mb,
  5          (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
  6   from dba_tables
  7    where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    6200034 821.224054    855.429688       34.2056338

SQL>   select (select nvl(sum(s.bytes/1024/1204), 0)                              -- the table segment size  
  2             from dba_segments s
  3            where s.owner = upper('SYS')
  4              and (s.segment_name = upper('LOB1'))) +
  5          (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob segment size  
  6             from dba_segments s, dba_lobs l
  7            where s.owner = upper('SYS')
  8              and (l.segment_name = s.segment_name and
  9                  l.table_name = upper('LOB1') and
 10                  l.owner = upper('SYS'))) +
 11          (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob index size  
 12             from dba_segments s, dba_indexes i
 13            where s.owner = upper('SYS')
 14              and (i.index_name = s.segment_name and
 15                  i.table_name = upper('LOB1') and index_type = 'lob' and
 16                  i.owner = upper('SYS'))) "total_table_size_M"
 17     from dual;

total_table_size_M
------------------
        1059.79713

SQL>  select a.owner,  
  2          a.table_name,  
  3          a.column_name,  
  4          b.segment_name,
  5          b.segment_type,  
  6          ROUND(b.BYTES / 1024 / 1024)  
  7     from dba_lobs a, dba_segments b  
  8    where a.segment_name = b.segment_name  
  9      and a.owner = 'SYS'  
 10      and a.table_name = 'LOB1'  
 11   union all  
 12   select a.owner,  
 13          a.table_name,  
 14          a.column_name,  
 15          b.segment_name,
 16          b.segment_type,  
 17          ROUND(b.BYTES / 1024 / 1024)  
 18     from dba_lobs a, dba_segments b  
 19    where a.index_name = b.segment_name  
 20      and a.owner = 'SYS'  
 21      and a.table_name = 'LOB1';

OWNER           TABLE_NAME           COLUMN_NAME     SEGMENT_NAME                   SEGMENT_TYPE       ROUND(B.BYTES/1024/1024)
--------------- -------------------- --------------- ------------------------------ ------------------ ------------------------
SYS             LOB1                 TEXT            SYS_LOB0000089545C00002$$      LOBSEGMENT                              329
SYS             LOB1                 TEXT            SYS_IL0000089545C00002$$       LOBINDEX                                  3

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 M from dba_segments where segment_name='LOB1' and owner='SYS';

OWNER           SEGMENT_NAME                   PARTITION_NAME                          M
--------------- ------------------------------ ------------------------------ ----------
SYS             LOB1                                                             859.625


开始shrink lob字段：
SQL> alter table sys.lob1 modify lob (text) (shrink space cascade);


Table altered.

SQL>  select table_name,
  2          num_rows,
  3          avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
  4          blocks * 8 / 1024 high_water_mb,
  5          (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
  6   from dba_tables
  7    where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    6200034 821.224054    855.429688       34.2056338

SQL> analyze table lob1 compute statistics;

Table analyzed.

SQL> select table_name,
  2          num_rows,
  3          avg_row_len * num_rows / 1024 / 1024 / 0.9 actual_mb,
  4          blocks * 8 / 1024 high_water_mb,
  5          (blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9) need_recovery_mb
  6   from dba_tables
  7    where table_name = 'LOB1' and owner='SYS';

TABLE_NAME             NUM_ROWS  ACTUAL_MB HIGH_WATER_MB NEED_RECOVERY_MB
-------------------- ---------- ---------- ------------- ----------------
LOB1                    6200034 821.224054    855.429688       34.2056338

SQL> select (select nvl(sum(s.bytes/1024/1204), 0)                              -- the table segment size  
  2             from dba_segments s
  3            where s.owner = upper('SYS')
  4              and (s.segment_name = upper('LOB1'))) +
  5          (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob segment size  
  6             from dba_segments s, dba_lobs l
  7            where s.owner = upper('SYS')
  8              and (l.segment_name = s.segment_name and
  9                  l.table_name = upper('LOB1') and
 10                  l.owner = upper('SYS'))) +
 11          (select nvl(sum(s.bytes/1024/1024), 0)                              -- the lob index size  
 12             from dba_segments s, dba_indexes i
 13            where s.owner = upper('SYS')
 14              and (i.index_name = s.segment_name and
 15                  i.table_name = upper('LOB1') and index_type = 'lob' and
 16                  i.owner = upper('SYS'))) "total_table_size_M"
 17     from dual;

total_table_size_M
------------------
        1032.10963

SQL> select a.owner,  
  2          a.table_name,  
  3          a.column_name,  
  4          b.segment_name,
  5          b.segment_type,  
  6          ROUND(b.BYTES / 1024 / 1024)  
  7     from dba_lobs a, dba_segments b  
  8    where a.segment_name = b.segment_name  
  9      and a.owner = 'SYS'  
 10      and a.table_name = 'LOB1'  
 11   union all  
 12   select a.owner,  
 13          a.table_name,  
 14          a.column_name,  
 15          b.segment_name,
 16          b.segment_type,  
 17          ROUND(b.BYTES / 1024 / 1024)  
 18     from dba_lobs a, dba_segments b  
 19    where a.index_name = b.segment_name  
 20      and a.owner = 'SYS'  
 21      and a.table_name = 'LOB1';

OWNER           TABLE_NAME           COLUMN_NAME     SEGMENT_NAME                   SEGMENT_TYPE       ROUND(B.BYTES/1024/1024)
--------------- -------------------- --------------- ------------------------------ ------------------ ------------------------
SYS             LOB1                 TEXT            SYS_LOB0000089545C00002$$      LOBSEGMENT                              301
SYS             LOB1                 TEXT            SYS_IL0000089545C00002$$       LOBINDEX                                  3

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,BYTES/1024/1024 M from dba_segments where segment_name='LOB1' and owner='SYS';

OWNER           SEGMENT_NAME                   PARTITION_NAME                          M
--------------- ------------------------------ ------------------------------ ----------
SYS             LOB1                                                             859.625

验证证明shrink lob字段会收缩lobsegment的大小：

最后查询索引有效性：
select OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME,STATUS,LAST_ANALYZED,DEGREE,PARTITIONED from dba_indexes where table_name='LOB1' and table_owner='SYS';

OWNER           INDEX_NAME                     TABLE_OWNER     TABLE_NAME           TABLESPACE_NAME STATUS     LAST_ANALYZE DEGREE     PAR
--------------- ------------------------------ --------------- -------------------- --------------- ---------- ------------ ---------- ---
SYS             SYS_IL0000089545C00002$$       SYS             LOB1                 USERS           VALID                   0          NO

select INDEX_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where table_name='LOB1' and table_owner='SYS';

no rows selected


注意：shrink回收表后索引依然有效；但是以move方式回收表则需要重建索引（rebuild);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2144139/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2144139/