3.3.1 Determining If a Table Is Compressed

Query the *_TABLES or *_TAB_PARTITIONS data dictionary views to determine whether a table or partitioned is compressed.

• Query the *_TABLES data dictionary views to determine table compression.

  In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column.

  SQL> SELECT table_name, compression, compress_for FROM user_tables;
   
  TABLE_NAME       COMPRESSION   COMPRESS_FOR
  ---------------- ------------  -----------------
  T1               DISABLED
  T2               ENABLED       BASIC
  T3               ENABLED       OLTP
  T4               ENABLED       QUERY HIGH
  T5               ENABLED       ARCHIVE LOW

  For partitioned tables, the COMPRESSION column is NULL in the *_TABLES data dictionary views.
• For partitioned tables, query the *_TAB_PARTITIONS data dictionary views.

  The COMPRESSION column of the *_TAB_PARTITIONS views indicates the table partitions that are compressed. The COMPRESS_FOR column indicates the compression method in use for the table or partition.

  SQL> SELECT table_name, partition_name, compression, compress_for
    FROM user_tab_partitions;
  
  TABLE_NAME  PARTITION_NAME   COMPRESSION   COMPRESS_FOR
  ----------- ---------------- -----------   ------------------------------
  SALES       Q4_2004          ENABLED       ARCHIVE HIGH
    ...
  SALES       Q3_2008          ENABLED       QUERY HIGH
  SALES       Q4_2008          ENABLED       QUERY HIGH
  SALES       Q1_2009          ENABLED       OLTP
  SALES       Q2_2009          ENABLED       OLTP