Oracle exp compress=Y Compress option in export (EXP) utility


The Eucharistic Miracles of the World
Export is the oracle utility to take the export the logical data as backup. There are many options with this tool and among them Compress=[y]/n is very useful. This is not for compressing export dump file. If you take export of a table with compress=Y option, during the import the table the total number of current extents will be consolidated into one single extent. It means there would be only one extent after the import.  The default value for the compress option is Y.
This parameter is applicable only for export (exp) not for import (imp). LOB objects data cannot be compressed where as it uses its own storage values during the export.
Scenario: Where to apply
 If the expected next extent size is larger (example because of PCTINCREASE parameter) than the tablespace free space, then we get “Unable to extent next extent” during the space allocation for the next extent. Suppose we are in state of not to increase the tablespace size to accommodate the new extent and the space needed for the expected data is less the tablespace free space, we can go ahead with export of table with compress=Y option. Drop the table and import table. This will create a new table with one extent.
Syntax: exp userid=transaction/welcome1 file=transtbl.dmp log=transtbl.log tables=trans compress=y



Post a comment


Oracle (575) General (60) Unix (47) Blog (23) Script (12) OCI (3) SQL* Loader (3) Software (1) Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer