Shrink

Go Up to Available object actions by DBMS

For details on using Shrink operations to save or reclaim space, see the following topics:

• Shrinking SQL Server databases
• Shrinking Oracle rollback segments
• Shrinking Oracle tables or indexes

Shrinking SQL Server databases

You can reclaim space from a database that is too large.

To Shrink a SQL Server Database

1. Initiate a Shrink action against one or more databases. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying the settings on the Shrink wizard:

Step	Description
Action Options	Displays the databases you selected and lets you work with the following settings:
	Move data pages to beginning of file before shrink	Select to move data pages to the beginning of the file before the shrink.
	Release All Unused Space	Deselect to set the target free space to retain, and then in the Target Free Space to Retain (percent) box, type the new value of free space to retain. The new size for the database must be at least as large as the Minimum Allowable Size displayed in the Current File Size box.
	Target free space percent after shrink	Lets you specify the target free space percent after the shrink.
Preview	Preview the DDL generated from your choices. For more information, see Preview.

3. Click Execute. For information on the other options, see Preview and Scheduling.

Shrinking Oracle rollback segments

You can shrink the size of rollback segments. The proper sizing of rollback segments is critical to their overall performance. Performance degrades whenever a rollback segment must extend, wrap or shrink in response to transaction loads. Ideally, you want to make the extents of rollback segments as small as possible while still ensuring that each transaction can fit into a single extent.

After an abnormally large transaction load, you might consider shrinking a rollback segment to eliminate unnecessary space. Oracle lets you shrink a rollback segment manually by a specific amount or back to its Optimal Size.

Important Notes

For Oracle 9 or later, Shrink is not available if auto-UNDO management is enabled.

To Shrink the Size of One or More Rollback Segments

1. Initiate a Shrink action against one or more rollback segments. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying the settings on the Shrink wizard:

Step	Description
Action Options	Displays the rollback segments you selected and lets you work with the following settings:
	Specify the size...	If you do not provide a specific number the Rollback Segment uses the OPTIMAL value specified in the Storage clause. If an OPTIMAL value is not specified, the size defaults to the MINEXTENTS value of the Storage clause.
Preview	Preview the DDL generated from your choices. For more information, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.

Shrinking Oracle tables or indexes

You can shrink the size of tables or indexes.

Important Notes

Shrink is only available for tables in which the Row Movement property is enabled. For information on setting the Row Movement when creating or editing a table, see Tables Wizard (Oracle) and Tables Editor (Oracle).

To Shrink the Size of One or More Indexes or Tables

1. Initiate a Shrink action against one or more indexes or tables. For more information see Initiating an object operation.
2. Use the following table as a guide to understanding and modifying the settings on the Shrink wizard:

Step	Description
Action Options	Displays the objects you selected and lets you work with the following settings:
	Compact	Enable Compact to restrict shrinking to defragmenting the segment space and compact rows.
	Cascade	Enable Cascade to simultaneously shrink all dependent objects.
Dependencies	Lists referring and referenced objects potentially impacted by the change. For details, see Dependencies.
Preview	Preview the DDL generated from your choices. For more information, see Preview.

3. Click Execute. For information on the scheduling option, see Scheduling.