Oracle 如何在将lob归零后回收空间

在这个包含blob的表上，直到今天已经填满了32GB的表空间，我通过使用UPDATE语句将它们设置为NULL来删除了很大比例的blob (当然，其余列保持不变)。实际上没有释放任何空间，因此我不得不添加第二个数据文件4gb，以允许插入查询工作。我做了一个 “改变表X移动”，没有任何好处。我做了一个 “alter table X收缩空间”，它实际上增加了第二个数据文件的占用率，并使第一个不受影响。

我在网上找到了一个实用程序 (https://oracle-base.com/dba/script.php?category=monitoring&file=ts_free_space.sql)，它计算出可以节省多少空间，它使我的用户ts只有4004mb可以释放，而我希望第二个数据文件中至少有10gb (4gb) 基本上应该是空的，加上最初填充到容量 = 32gb的第一个数据文件的至少6gb)。

释放空间似乎是不可能的。设置为空是个坏主意吗？为什么Oracle拒绝将任何空间视为可回收空间？

很明显，我不能有一个持续增长的ts ....在这个特定的数据库上，如果您认为有帮助，我可以继续删除我更新的那些行，但是在其他安装上，我只需要将BLOB设置为null，然后keep行。

更具体地说，这个查询:

 select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible
  from dba_data_files a,
         ( select file_id, max(block_id+blocks) hwm
             from dba_extents
            group by file_id ) b
    where a.file_id = b.file_id;

。.. 给出此结果 (用户 *.dbf是相关文件，01是已填充的旧文件，02是我今天创建的新文件)

FILE_NAME                                                                 HWM TOTAL_BLOCKS SHRINKAGE_POSSIBLE
------------------------------------------------------------------ ---------- ------------ ------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf                      4194176      4194240                 65
/home/oracle/app/oracle/oradata/orcl/users02.dbf                       183816       524288             340473
/home/oracle/app/oracle/oradata/orcl/system01.dbf                       89984        90880                897
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf                       73640        78080               4441
/home/oracle/app/oracle/oradata/orcl/example01.dbf                      10392        12800               2409
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf                      53376        64640              11265

-乔治

这里要注意的关键是您的lob (clob或blob) 是内联还是离线。

如果它们是内联的，它们将进入表格段。否则它们会进入LOB部分。

要内联:

列值必须为 “小” (小于4,000字节)
lob设置为 “在行中启用存储” (默认)

http://docs.oracle.com/database/122/ADLOB/LOB-storage-with-applications.htm#ADLOB45273

如果所有行的lob都是内联的，则在将它们设为null之后，您可以通过移动或缩小表来回收它们使用的空间。

但是一旦lob离线，它就会离线。因此，要收回空间，您需要移动/缩小lob，而不是表!

在下面的演示中，我从一些小的内联lob开始。将这些设置为null后，缩小表收回空间:

create tablespace lobtest
logging datafile 'lobtest.dbf' size 10m
extent management local
segment space management auto;

create table t ( x clob ) lob (x) store as (enable storage in row)
  enable row movement tablespace lobtest;

insert into t 
  select lpad('x', 1000, 'x') from dual
  connect by level <= 1000;
  
commit;

select segment_name from user_lobs
where  table_name = 'T';

SEGMENT_NAME               
SYS_LOB0000116998C00001$$ 

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES    
SYS_LOB0000116998C00001$$  65536    
T                          2097152  

update t 
set    x = null;

commit;

alter table t shrink space;

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES  
SYS_LOB0000116998C00001$$  65536  
T                          65536 

但是，如果我重击一个单一的大clob，它就会进入lob部分。所以要把这个空间清理出来后，我需要缩小lob:

truncate table t;

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES  
SYS_LOB0000116998C00001$$  65536  
T                          65536 

declare
  l_clob   clob;
  l_size   number := 10000;
begin
  insert into t values ( empty_clob() ) returning x into l_clob;

  for i in 1..10 loop
    dbms_lob.writeappend(
      l_clob,
      l_size,
      lpad('x', l_size, 'x')
    );

    l_size := 31191;
  end loop;

  commit;
end;
/


select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES   
SYS_LOB0000116998C00001$$  458752  
T                          65536   

update t
set    x = null;

commit;

alter table t shrink space;

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES   
SYS_LOB0000116998C00001$$  458752  
T                          65536 

alter table t modify lob (x) (shrink space);

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES  
SYS_LOB0000116998C00001$$  65536  
T                          65536  

I can't have a ts that keeps on growing, obviously....

真的吗？数据库不应该随着时间的推移而增长吗？;)