How to Verify Advanced Compression in Oracle
Verify Advanced Compression usage in Oracle:
Step 1: Verification in Tables:
| SQL> col owner form a15 SQL> col TABLE_NAME form a32 SQL> SELECT table_name,compression,compress_for FROM dba_tables 2 where compression=’ENABLED’; no rows selected SQL> |
- Note : If the output is no rows selected that means not using advanced compression option.
Step 2:Verification in Partitions:
| SQL> col owner form a23 SQL> col table_name form a32 SQL> col instance_name form a15 SQL> col name form a10 SQL> col host_name form a21 SQL> col compress_for form a20 SQL> col PARTITION_NAME form a31 SQL> set lines 199 SQL> set pages 100 SQL> select i.host_name,i.instance_name,d.name,b.owner,a.table_name,PARTITION_NAME,a.COMPRESSION,a.COMPRESS_FOR 2 from dba_tab_partitions a,dba_tables b,v$instance i,gv$database d where a.compression=’ENABLED’ and a.COMPRESS_FOR=’ADVANCED’ and a.table_name=b.table_name; no rows selected SQL> |
- Note: If the output is no rows selected that means not using advanced compression option.
Another example can be verified in the following link:
Step 3: Verify Compress Parameter in the expdp activity:
If we are using compress = Yes in expdp parfile or expdp command then advanced compression usage will be registered.
See Also: