Oracle data pump PCTSPACE tips Oracle Database Tips by Donald Burleson

Question: The TRANSFORM Parameter can be used in Data Pump Import to modify the storage attributes of the DDL that creates the objects during the import job.
One of the values accepted by this parameter is PCTSPACE. As stated in the documentation, this value "represents the percentage multiplier used to alter extent allocations and the size of data files." This explanation is not clear to me.

Does any one know the advantage of using PCTSPACE parameter with Data Pump Import?
 

Answer:  For complete details on using transform, see the book "Oracle Utilities".  The docs note this on using PCTSPACE:

PCTSPACE - The value supplied for this transform must be a number greater than zero. It represents the percentage multiplier used to alter extent allocations and the size of data files.

With this feature, you can reduce the amount of space required for tablespaces by shrinking tablespace storage allocation. Large datafiles in production systems can be reduced so that smaller datafiles are created during Import on test systems.

The benefit of this feature is the ability to reduce the amount of tablespace storage required for datafiles.

Reducing tablespace & file allocation with PCTSPACE

The PCTSPACE parm is commonly used with TRANSFORM to downsize tablespaces (and the underlying data files) when lots of rows have been removed.  For example, here we use PCTSPACE to downsize a tablespace:

root> impdp transform=pctspace:25

This tells the impdp (import) to re-size the tablespace to 25% of its original size.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.