Compression -- 1b : (more on) BASIC Table Compression

In the previous blog, I demonstrated creating an empty table with BASIC Compression and then populating it.

What if you have a pre-existing table with data that you want to compress ?

Here I start with a table that already has rows but doesn't have compression configured.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name = 'REGULAR_1';

TABLESPACE_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
HEMANT          TABLE        49

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
  2  from user_tables
  3  where table_name = 'REGULAR_1';

  PCT_FREE     BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
 10  6224   364496       48.625

PDB1@ORCL>

I then proceed to rebuild it as a COMPRESSed table.

PDB1@ORCL> alter table regular_1 move compress;

Table altered.

PDB1@ORCL> exec dbms_stats.gather_table_stats('','REGULAR_1');

PL/SQL procedure successfully completed.

PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name = 'REGULAR_1';

TABLESPACE_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
HEMANT          TABLE        12

PDB1@ORCL> select pct_free, blocks, num_rows, blocks*8192/1048576
  2  from user_tables
  3  where table_name = 'REGULAR_1';

  PCT_FREE     BLOCKS NUM_ROWS BLOCKS*8192/1048576
---------- ---------- ---------- -------------------
  0  1448   364496      11.3125

PDB1@ORCL> 

Note how not only did the table size shrink to less than 12MB, the PCT_FREE also got  reset to 0 !
.
.
.

Compression -- 1 : BASIC Table Compression

Now, starting a new series of blog posts on Compression.

Beginning with  BASIC Table Compression.

Basic Table Compression was introduced in 9i Release 2 (9.2.0).  It is free with the Enterprise Edition.  Basic Compression works only with Direct Path (Bulk Load) INSERTs.  It does not apply to "normal" INSERT and UPDATE operations.
Basic Compression is actually "de-duplication".  It identifies values that are repeated within the same database block and replaces them with tokens mapped to the list of values.   Note the section that I have underlined.  De-duplication does not span database blocks.

Here is a first demo of Basic Compression using INSERT /*+ APPEND */  (for Direct Path Insert).

I start with a table that is a multiplied copy of DBA_OBJECTS, named as SOURCE_DATA.

PDB1@ORCL> show user
USER is "HEMANT"
PDB1@ORCL> select count(*) from source_data;

  COUNT(*)
----------
    364496

PDB1@ORCL> 
PDB1@ORCL> select tablespace_name, segment_type, bytes/1048576
  2  from user_segments
  3  where segment_name = 'SOURCE_DATA';

TABLESPACE_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
HEMANT          TABLE        49

PDB1@ORCL> 

I then create table to hold compressed data.  Note (let me repeat again : Basic Compression works only with DIRECT PATH INSERT).

PDB1@ORCL> create table compressed_1 tablespace hemant compress as select * from source_data where 1=2;

Table created.

PDB1@ORCL> insert /*+ APPEND */ into compressed_1
  2  select * from source_data;

364496 rows created.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL>
PDB1@ORCL> col segment_name format a30
PDB1@ORCL> select segment_name, segment_type, bytes/1048576
  2  from user_Segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        12
SOURCE_DATA         TABLE        49

PDB1@ORCL> 

So, a 49MB table is compressed down to 12MB.  What if I UPDATE these rows ?

PDB1@ORCL> update compressed_1
  2  set owner = owner;

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        22
SOURCE_DATA         TABLE        49

PDB1@ORCL> 

Updating only a single column without changing the length of the data in that column has increased the size of the table.

Let me continue the UPDATE experiment further, without increasing the length of data in any columns.

PDB1@ORCL> update compressed_1
  2  set owner = owner, object_name = object_name, subobject_name = subobject_name, object_id = object_id,
  3  data_object_id = data_object_id, object_type = object_type, created = created
  4  /  

364496 rows updated.

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> select segment_name, segment_type, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME         SEGMENT_TYPE   BYTES/1048576
------------------------------ ------------------ -------------
COMPRESSED_1         TABLE        22
SOURCE_DATA         TABLE        49

PDB1@ORCL> 

No further degradation ? (unless my UPDATE expands the size of data in one or more column(s)).

Note a difference between the two tables :

PDB1@ORCL> select table_name, compression, compress_for, pct_free
  2  from user_tables;

TABLE_NAME         COMPRESS COMPRESS_FOR    PCT_FREE
------------------------------ -------- ------------------------------ ----------
COMPRESSED_1         ENABLED BASIC     0
SOURCE_DATA         DISABLED            10

PDB1@ORCL> 

The Compressed table is created with PCT_FREE=10.  PCT_FREE=0  (Note that this can also have an impact when you issue UPDATEs subsequently, UPDATEs that increase the size of data in one or more columns).

UPDATE : In 12c the syntax for BASIC Compression is "ROW STORE COMPRESS BASIC". Simply specifying "COMPRESS" or "COMPRESS BASIC" is supported for backward compatibility.

.
.
.

RMAN : Unused Block Compression and Null Block Compression

To quote the 11.2 documentation,  "Although it is referred to as block compression, it might be helpful to think of block compression as block skipping".  11.2 differentiates between Unused Block Compression and Null Block Compression.
UPDATE 28-Feb-16 : Please note the list of 5 pre-requisites for Unused Block Compression.

In response to a community thread, here is a quick demo to see both Null Block Compression and Unused Block Compression.

I start with a 100MB tablespace.

[oracle@ora11204 Desktop]$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:37:47 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create tablespace hemant;

Tablespace created.

SQL> alter user hemant quota unlimited on hemant;

User altered.

SQL> select file_name, bytes/1048576 from dba_data_files
  2  where tablespace_name = 'HEMANT';

FILE_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
   100


SQL> 

First I take a backup of this empty tablespace.

RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 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=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp tag=TAG20160219T224255 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904257776_cdgbhkcx_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 

This 100MB datafile backed up to 1.03MB is Null Blocm compression.

I next load data into it and  then take a backup.

[oracle@ora11204 Desktop]$ sqlplus hemant/hemant

SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 19 22:45:12 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table hkc_all_obj tablespace hemant
  2  as select * from dba_objects;

Table created.

SQL> insert into hkc_all_obj select * from hkc_all_obj;

28105 rows created.

SQL> /

56210 rows created.

SQL> /

112420 rows created.

SQL> /

224840 rows created.

SQL> commit;              

Commit complete.

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME
--------------------------------------------------------------------------------
BYTES/1048576
-------------
HKC_ALL_OBJ
    49


SQL> 
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 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=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp tag=TAG20160219T224721 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258049_cdgbr179_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    49.67M     DISK        00:00:04     19-FEB-16      
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224721
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807559    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 

So, I now have 49.67MB backup of the datafile in the tablespace. What happens if I drop all objects (in this case only the 1 table) in that tablespace, with RECYLEBIN ?

SQL> show parameter recycle

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle       string
db_recycle_cache_size       big integer 0
recyclebin        string  on
SQL> drop table hkc_all_obj;

Table dropped.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
  2  from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME        SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT         6272    49


SQL> 
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 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=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp tag=TAG20160219T225023 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258224_cdgbxjg4_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    49.67M     DISK        00:00:04     19-FEB-16      
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224721
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807559    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    49.67M     DISK        00:00:00     19-FEB-16      
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T225023
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807909    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 

The latest backup (BackupSet 29) is still 49.67MB although, technically, the tablespace has no Tables/Indexes.  It does have something in the RECYCLEBIN.

Let me purge the RECYCLEBIN and then run a fresh backup.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
  2  from user_recyclebin;

OBJECT_NAME         ORIGINAL_NAME
------------------------------ --------------------------------
TS_NAME        SPACE SPACE*8192/1048576
------------------------------ ---------- ------------------
BIN$LCGWc71HDSzgUwEAAH/27g==$0 HKC_ALL_OBJ
HEMANT         6272    49


SQL> purge hkc_all_obj;
purge hkc_all_obj
      *
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge table hkc_all_obj;

Table purged.

SQL> select object_name, original_name, ts_name, space, space*8192/1048576
  2  from user_recyclebin;

no rows selected

SQL> 
RMAN> backup tablespace hemant;

Starting backup at 19-FEB-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 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=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
channel ORA_DISK_1: starting piece 1 at 19-FEB-16
channel ORA_DISK_1: finished piece 1 at 19-FEB-16
piece handle=/u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp tag=TAG20160219T225323 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-FEB-16

Starting Control File and SPFILE Autobackup at 19-FEB-16
piece handle=/u02/FRA/ORCL/autobackup/2016_02_19/o1_mf_s_904258404_cdgc351n_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-FEB-16

RMAN> list backup of tablespace hemant;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
25      Full    1.03M      DISK        00:00:01     19-FEB-16      
        BP Key: 25   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224255
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224255_cdgbhhyh_.bkp
  List of Datafiles in backup set 25
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4806951    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
27      Full    49.67M     DISK        00:00:04     19-FEB-16      
        BP Key: 27   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T224721
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T224721_cdgbqw0k_.bkp
  List of Datafiles in backup set 27
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807559    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
29      Full    49.67M     DISK        00:00:00     19-FEB-16      
        BP Key: 29   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T225023
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225023_cdgbxh72_.bkp
  List of Datafiles in backup set 29
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4807909    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31      Full    1.03M      DISK        00:00:00     19-FEB-16      
        BP Key: 31   Status: AVAILABLE  Compressed: NO  Tag: TAG20160219T225323
        Piece Name: /u02/FRA/ORCL/backupset/2016_02_19/o1_mf_nnndf_TAG20160219T225323_cdgc33tc_.bkp
  List of Datafiles in backup set 31
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4808966    19-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf

RMAN> 
[oracle@ora11204 Desktop]$ ls -l /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
-rw-rw----. 1 oracle oracle 104865792 Feb 19 22:53 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cdgb60g4_.dbf
[oracle@ora11204 Desktop]$ 

Aha ! BackupSet 31 is now 1.03MB only.  So, Unused Block Compression has kicked in !  The blocks that belonged to that table are yet formatted and in prior versions that did not have Unused Block Compression would still be backed up even though the table has been dropped.
(Note : I did not shrink the datafile, it is still 100MB).


UPDATE 28-Feb-16 :  Testing for TRUNCATE

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME           BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ               49

SQL> 
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 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=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp tag=TAG20160228T190655 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022445_cf5o7lyq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    49.55M     DISK        00:00:22     28-FEB-16      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160228T190655
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4776386    28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN> 
SQL> show parameter recyclebin;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
recyclebin        string  on
SQL> truncate table hkc_all_obj;

Table truncated.

SQL> select segment_name, bytes/1048576
  2  from user_segments
  3  where tablespace_name = 'HEMANT';

SEGMENT_NAME           BYTES/1048576
--------------------------------------------------------------------------------- -------------
HKC_ALL_OBJ            .0625

SQL> 
RMAN> backup tablespace hemant;

Starting backup at 28-FEB-16
using channel ORA_DISK_1
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=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf
channel ORA_DISK_1: starting piece 1 at 28-FEB-16
channel ORA_DISK_1: finished piece 1 at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp tag=TAG20160228T190946 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 28-FEB-16

Starting Control File and SPFILE Autobackup at 28-FEB-16
piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2016_02_28/o1_mf_s_905022589_cf5oczwq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-FEB-16

RMAN> list backup of tablespace hemant completed after "trunc(sysdate)";


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    49.55M     DISK        00:00:22     28-FEB-16      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20160228T190655
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190655_cf5o6lw3_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4776386    28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.53M      DISK        00:00:01     28-FEB-16      
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20160228T190946
        Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2016_02_28/o1_mf_nnndf_TAG20160228T190946_cf5octqc_.bkp
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  11      Full 4776656    28-FEB-16 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_hemant_cf5nc41d_.dbf

RMAN> 

The TRUNCATE did reduce the size of the table and the backup !
.
.
.