I want steps to in Oracle Database to find whether Compression is happening or not on a particular table

CodePudding user response：

Please perform below steps:

1. First execute analyze on your table.

   analyze table YOUR_TABLE_NAME compute statistics;

2. Execute below query to check the compression stats:

   select b.name, b.subname, a.AVGROWSIZE_NC, a.AVGROWSIZE_C, a.NBLK_NC, a.NBLK_ADVANCED, a.NBLK_EHCC, a.NROWS_NC, a.NROWS_ADVANCED, a.NROWS_EHCC from sys.compression_stat$ a, sys.obj$ b where a.obj# = b.obj#;

3. Execute the compression on your table

4. Analyze your table again (same first step)

5. Execute the SELECT query.. same as step 2nd.