Oracle RMAN未使用的块压缩，针对TABLE删除，而针对TABLE使用lob列压缩。

嗨，汤姆，

我只是想了解RMAN未使用的块压缩，对TABLE使用LOB列压缩和TABLE删除。
考虑表'EMPLOYES'中列'DATA'的LOB数据，该表位于SCOTT架构下。

- lob段
从DBA_LOBS中选择段名，其中所有者='SCOTT'且table_NAME ='雇员'且列名='数据'；
段名
--------------------------
LOBD_EMPLOYES_DATA

情况1 ：使用LOB列压缩针对TABLE的RMAN未使用的块压缩
=================================================================================
第1部分：前LOB压缩信息
=================================
1- LOBSEGM的存储大小
SELECT SUM(BYTES)/1024/1024 "存储_MB" FROM DBA_EXTENTS WHERE 段名 = 'LOBD_EMPLOYES_DATA';
存储_MB
----------
183420
2-确定LOB数据的大小
从SCOTT.EMPLOYES中选择和(dbms_lob.get)/1024/1024/1024大小MB ；
大小
----------
16324.356
3-数据库RMAN备份大小- 1.5 TB
使用的RMAN备份脚本如下

运行{
在上配置备份优化；
配置设备类型磁盘并行度2备份类型到备份集；
配置通道设备类型磁盘最大块大小8G ；

交叉检查所有归档日志；
删除强制不提示已过期的归档日志全部；
备用
文件perset 1
完全的
format '/备用//xxxx.set%s.piece%p'
（数据库）加上归档日志；
}


第2节： POST-LOB压缩信息【我们使用gzip算法压缩LOB数据】
===================================
1- LOBSEGM的存储大小 remains the same after the LOB compression.
SELECT SUM(BYTES)/1024/1024 "存储_MB" FROM DBA_EXTENTS WHERE 段名 = 'LOBD_EMPLOYES_DATA';
存储_MB
----------
183420

2- LOB数据的大小得到了减小。
从SCOTT.EMPLOYES中选择和(dbms_lob.get)/1024/1024/1024大小MB ；
大小
----------
6093.90531

非LOB数据的大小为3。这说明了撤消数据的大小（已过期+未过期或pctversion ） +未使用的空间
183420 - 6093 = 177327 MB

4-为了回收未使用的空间，我使用了lob段上的收缩空间。
改变表SCOTT.EMPLOYES修改lob(DATA) （收缩空间） ；
表已更改。
已通过： 05:44:20.71

5 - LOBSEGF的存储大小从183 GB减少到8.9 GB
SELECT SUM(BYTES)/1024/1024 "存储_MB" FROM DBA_EXTENTS WHERE 段名 = 'LOBD_EMPLOYES_DATA';
存储_MB
----------
8970

在收缩空间过程中创建了许多空块【以前使用过的】，因为它将大小从183 GB减少到8.9 GB。
6 - I was under the impression that database RMAN 备用 should be reduced by ~170GB [ 183-8.9] as RMAN uses unused block compression, which should skip the empty blocks created due to shrink space.
供你参考。我们的环境满足RMAN使用未使用的块压缩的所有条件。我也做了测试，通过参考https://hemantoracledba.blogspot.com/2016/02/rman-unused-block-compression-and-null.html
.
7- Have taken the fresh database RMAN using the script mentioned in section 1.3 .To my surprise database RMAN 备用 remain the same i.e 1.5TB.

Could you please throw some lihgt on what am I missing here ? why is database RMAN 备用 size not getting reduced ?


情况2 : RMAN未使用的数据块压缩，防止TABLE丢弃
===========================================================
- I could see the difference in RMAN 备用 of the tablespace 'TESTLOB' size with respect to PRE and POST table 'EMPLOYEES' drop . Hence RMAN is using the unused block compression here.

RMAN对于lob压缩和表丢弃的行为是否不同？

谢谢，
拉姆

我尝试了不同的排列

-安全文件，修改以压缩
-基本文件、删除行和缩小空间

我在这里看到了我对所有情况的期望，例如，一个拽出，一个拽出空间减小了段的大小，备份也相应地减小了

SQL> create tablespace DEMO datafile 'C:\ORACLE\ORADATA\NP12\DEMO.DBF' size 200m;

Tablespace created.

SQL> create table t (
  2    pk int,
  3    c clob )
  4  lob(c) store as securefile ( nocompress)
  5  tablespace demo;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  327680
SYS_IL0000203091C00002$$            65536
SYS_LOB0000203091C00002$$       184745984

RMAN> backup tablespace demo format 'c:\temp\demo.1';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.1 tag=TAG20170219T173436 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 19-FEB-17

C:\Users\comcdona>dir c:\temp\DEMO.1
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of c:\temp

19/02/2017  05:34 PM       166,674,432 DEMO.1

SQL> drop table t purge;

Table dropped.

RMAN> backup tablespace demo format 'c:\temp\demo.2';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.2 tag=TAG20170219T173645 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-17


C:\temp>dir DEMO.2
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:36 PM         1,089,536 DEMO.2

SQL> alter table T modify lob(c) (compress high);

Table altered.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  786432
SYS_IL0000203094C00002$$            65536
SYS_LOB0000203094C00002$$       184745984

RMAN> backup tablespace demo format 'c:\temp\demo.3';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.3 tag=TAG20170219T173938 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-17

RMAN> quit


Recovery Manager complete.

C:\temp>dir demo.3
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:39 PM       169,918,464 DEMO.3
               1 File(s)    169,918,464 bytes
               0 Dir(s)  47,581,122,560 bytes free
               
SQL> alter table T move lob ( c ) store as securefile (compress high);

Table altered.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  655360
SYS_IL0000203094C00002$$            65536
SYS_LOB0000203094C00002$$          131072


SQL> create table t (
  2    pk int,
  3    c clob )
  4  lob(c) store as basicfile
  5  tablespace demo;

Table created.

SQL>
SQL> insert into t
  2  select rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  393216
SYS_IL0000203103C00002$$            65536
SYS_LOB0000203103C00002$$       167772160

SQL> delete from t
  2  where rownum < 4000;

3999 rows deleted.

SQL> commit;

Commit complete.


RMAN> backup tablespace demo format 'c:\temp\demo.4';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=207 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.4 tag=TAG20170219T174611 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-17

C:\temp>dir DEMO.4
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:46 PM       169,910,272 DEMO.4

SQL> alter table t modify lob (c) (shrink space);

Table altered.

SQL> select segment_name, bytes
  2  from dba_segments
  3  where tablespace_name = 'DEMO';

SEGMENT_NAME                        BYTES
------------------------------ ----------
T                                  393216
SYS_IL0000203103C00002$$           458752
SYS_LOB0000203103C00002$$        33488896

RMAN> backup tablespace demo format 'c:\temp\demo.5';

Starting backup at 19-FEB-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=403 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00011 name=C:\ORACLE\ORADATA\NP12\DEMO.DBF
channel ORA_DISK_1: starting piece 1 at 19-FEB-17
channel ORA_DISK_1: finished piece 1 at 19-FEB-17
piece handle=C:\TEMP\DEMO.5 tag=TAG20170219T174811 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-17

C:\temp>dir DEMO.5
 Volume in drive C is System
 Volume Serial Number is 7AD7-C05A

 Directory of C:\temp

19/02/2017  05:48 PM        35,692,544 DEMO.5