Oracle OLTP压缩

嗨，

我们已经达到90%的表空间（磁盘空间）已满，并且我们考虑压缩表，以便释放一些空间。

select 
segment_name,
segment_type,
bytes/1024/1024 MB
from user_segments 
where segment_type='TABLE' 
and segment_name='DV_XXXXXX_HST';  --- 4341 (Size in MB before COMPRESSION)  

然后我们为OLTP压缩表，使用

ALTER TABLE DV_XXXXXX_HST MOVE COMPRESS FOR OLTP; 

压缩后检查表的大小

select 
segment_name,
segment_type,
bytes/1024/1024 MB
from user_segments 
where segment_type='TABLE' 
and segment_name='DV_XXXXXX_HST';  --- 1856(Size in MB after COMPRESSION)  

但是，即使在压缩之后，块的编号也不会改变，如下面的查询所示
压缩前后的结果完全相同

 with tabspace as
(
  select
  t.*,
  round( ((t.num_rows*t.avg_row_len) / (1024*1024) ),2) as net_MB,
  round( ((t.blocks*32)/1024),2) as blocks_MB
  from dba_tables t
)
select
  table_name,
  partitioned,
  compression,
  compress_for,
  blocks,
  num_rows,
  net_MB,
  blocks_MB
from tabspace
where
  table_name like '%DV_XXXXXX_HST'   
;

TABLE_NAME,     PARTITIONED,   COMPRESSION,  COMPRESS_FOR,  BLOCKS,   NUM_ROWS,     NET_MB,        BLOCKS_MB
DV_XXXXXX_HST   NO         ENABLED        ADVANCED     128500    9485188     1058.36     4015.63

请帮助我们理解为什么表的块数在压缩后没有下降。

或者，我们的观点是否错误？

我们是否可以用其他方法来达到这个目标? （正在使用DBMS_REDREDEKINIING not sure... ）
如何将表中未使用的可用空间回收到表空间？

谢谢你，
克里希纳

压缩=在直接模式操作（ sqlldr、ctas、插入-append、改变表移动）期间*仅压缩数据。所有后续（也称为正常） DML将不会创建压缩数据。

对oltp =如上所述进行压缩，但对常规的dml活动也是如此。它不会在你（例如）添加一行时* *进行压缩，而是在块“已满”超过特定阈值时。因此，您不会看到“额外”压缩-在正常的dml活动期间，表将保持较好的压缩状态。

而且你需要一个'压缩oltp'的*许可证*。

下面的示例

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T as
  2  select * from dba_Objects
  3  order by owner, object_type;

Table created.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
  13631488

1 row selected.

SQL>
SQL> alter table T move compress;

Table altered.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
   4194304

1 row selected.

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T as
  2  select * from dba_Objects
  3  order by owner, object_type;

Table created.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
  13631488

1 row selected.

SQL>
SQL> alter table T move compress for oltp;

Table altered.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
   5242880

1 row selected.

SQL>
SQL>