Compressing read-only tablespaces
Prev Compressed files and databases Next

Compressing read-only tablespaces

In a large database environment, it is a common practice to keep static tablespaces that do not have any changes in read-only mode. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables and objects residing in the tablespace, regardless of a user's update privilege level. These kinds of read-only tablespaces are excellent candidates for compression. In some cases such as month end reports, there may be large queries executed against these read-only tablespaces. To make the report run faster, you can uncompress the tablespace on demand before running the monthly reports.

In the following example, a sporting goods company has its inventory divided into two tablespaces: winter_items and summer_items. In the end of the Spring season, you can compress the winter_item tablespace and uncompress the summer_item tablespace. You can do the reverse actions at end of the Summer season. The following example procedure performs these tasks.

To compress and uncompress tablespaces depending on the season

  1. Using SQL, get a list of files in each tablespace and store the result in the files summer_files and winter files.
    $ SQL> select file_name from dba_data_files where
tablespace_name = 'WINTER_ITEM';
Store the result in winter_files
$ SQL> select  file_name from dba_data_files where
tablespace_name = 'SUMMER_ITEM';
Store the result in summer_files
  2. Compress the winter_files file:
    $ /opt/VRTS/bin/vxcompress '/bin/cat winter_files'
  3. Uncompress the summer_files file:
    $ /opt/VRTS/bin/vxcompress -u '/bin/cat summer_files'
Prev Up Next
Compressing archive logs Home Compressing infrequently accessed table partitions