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
- Initiate a Shrink action against one or more databases. For more information see Initiating an object operation.
- 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
- Initiate a Shrink action against one or more rollback segments. For more information see Initiating an object operation.
- 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
- Initiate a Shrink action against one or more indexes or tables. For more information see Initiating an object operation.
- 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.