Review Space Usage
After you specify storage options for individual objects in the Customize Object Allocations page, the Reorg Manager performs a space audit and displays the results in the Space Usage Summary tab. Reorg Manager checks for the following:
- Free space in target tablespaces—Reorg Manager checks target tablespaces to see if they have enough free space for original and copy objects.
- Data block size—Reorg Manager checks to see if tablespaces for partitions and subpartitions from the same object use the same data block size. If these tablespaces use different sizes, a warning is raised. Make note of the objects listed in the warning. Then return to the Customize Object Allocations page and select tablespaces that use the same block sizes for these objects.
-
LiveReorg tablespace—If you are performing a live reorganization, Reorg Manager checks to see if the LiveReorg tablespace has enough space for LiveReorg objects. The LiveReorg tablespace and LiveReorg objects are used by QSA. The LiveReorg tablespace is specified with the agent’s LW_TABLESPACE parameter. Space checks by Reorg Manager consider the space needed for creating LiveReorg objects. They do not consider additional space that might be needed for object growth. If a live reorganization fails because a LiveReorg object cannot grow, a collection trigger error is displayed in the script log.
Note: Another space audit is performed when a script is run to make sure sufficient space is still available.
Space Usage Summary Tab
The Space Usage Summary tab displays information for segments and tablespaces involved in a reorganization. This includes original tablespaces, tablespaces being used on an interim basis, tablespaces to which objects are being relocated, and the LiveReorg tablespace.
For each segment, the information displayed includes current and ending space allocation values.
For each tablespace, the information displayed includes starting free space and ending free space.
Space Usage Messages
Space Manager may alert you to space availability with various messages. If a message indicates that a target tablespace or the LiveReorg tablespace does not have enough free space, open Tablespace Properties for the tablespace and add datafiles or increase datafile size. Leave Reorg Manager open while you do this. You can also return to previous windows and select different target tablespaces or adjust storage values for the objects being reorganized.
If Space Manager displays a message after estimating space usage (in the Reorg Manager or other areas of the application), review the message description and suggested guidelines below.
|
Message |
Description |
|---|---|
|
Insufficient Free Space |
When one or more target tablespaces do not have enough free space, the “insufficient free space” message is displayed at the bottom of the Review Space Usage window. Values for target tablespaces without enough free space are displayed in red. A negative value is displayed in the Lowest Usable Freespace column. |
|
Insufficient Contiguous Free Space |
When one or more target tablespaces do not have enough contiguous free space for more objects, a message with a list of these tablespaces and objects displays when you proceed to the Review Space Usage window.
 Click here to see an example.
To provide the contiguous space needed in a target tablespace or the LiveReorg tablespace, use one of the methods under “Space Usage Messages”. If your actions do not result in sufficient contiguous free space, one of the following messages is displayed in the Review Space Usage window:
|
|
Current Allocation Notes |
The Review Space Usage window sometimes displays a note indicating that some objects were sized based on current allocation instead of current usage. Current allocation and current usage are two sizing options for the Minimize Number of Extents global allocation strategy. You can select either one. However, the current usage option requires current statistics in the Space Manager repository. If statistics are not current for an object, current allocation is used instead of current usage.
Click here to see an example.
The note gives one of the following reasons for use of current allocation:
|
|
Underestimates and Overestimates |
Space checks can sometimes underestimate or overestimate the space required by an object. This happens when statistics in the Space Manager repository are not current. The results of incorrect estimates are as follows:
Even when space checks are accurate, “Freespace not available” errors can occur during script execution. This happens when extents are allocated in a different order than anticipated. For example, a space check might anticipate that larger extents will be allocated to larger objects first, but Oracle might allocate these extents to smaller objects first. As a result, they are not available for larger objects. |
Run and Monitor Scripts
Run and Schedule Scripts
About Running and Scheduling Scripts
There are several ways to execute a reorganization script. Space Manager allows you to manually execute a script, to schedule a script, or to store a script without scheduling it. You can execute or schedule a script from the reorganization wizard or from the Job Monitor.
How to Execute a Script
You can execute scripts using the following methods:
-
Run scripts in the Editor—Some scripts can be opened and executed in the Editor from the reorganization wizard or the Job Monitor. See Run Scripts from the Editor.
Important: Live reorganization scripts and scripts that use FastCopy or parallel QSA processes cannot be run from the SQL Editor. They must be scheduled and run by QSA. These scripts contain commands that must be interpreted by QSA. To run a script in QSA, it must be scheduled (even when executed immediately).
- Schedule scripts—You can schedule a script from a reorganization wizard, from the Job Monitor, or from script Properties. See Schedule Scripts.
- Schedule script generation—You can schedule a recurring script generation process for a LiveReorg script. Use this method to generate (and execute) the script at a later time or on a recurring basis. See Schedule Script Generation.
Script Execution Choices in Wizards
When you create a script in a reorganization wizard, you must either run the script immediately (in Editor, or by selecting Execute Immediately), schedule the script, store the script, or schedule script generation (LiveReorg only). See Schedule Script Execution in Wizards.
Tip: As an alternative to creating and executing a script in the Reorg Manager, you can save your Reorg Manager settings as a Reorg Plan to use later. See Save Reorg Manager Settings.
Before Running a Script
Review the following information prior to running a reorganization script.
Important Considerations and Best Practices
Review the following important considerations and best practices prior to running a reorganization script.
| Consideration / Best Practice | Description |
|---|---|
| Stop DML activity for Standard reorg scripts | Before you run a standard reorganization script, stop DML activity against the objects being reorganized. All DML activity should be suspended until the script completes successfully. |
| Same tables in multiple scripts | If the same tables are included in multiple scripts, do not run the scripts at the same time. |
| Tables that share referential integrity | If tables that share referential integrity are included in multiple scripts, do not run the scripts at the same time. |
| Script and Space Manager version compatibility | Execute a script using the same version of Space Manager that was used to generate the script. |
| Reduce time between script generation and execution | Reorganization scripts should be run as soon as possible after they are generated. If the data structures of the objects in the script are modified between script generation and execution, the DDL and DML statements in the script can cause errors and/or data loss during script execution. |
| Execute script only once | Each reorganization script should be run only once. If a live reorganization script is run more than once, the second run fails if any of the objects being reorganized have system-named constraints. You can avoid this problem by saving Reorg Manager settings. See Save Reorg Manager Settings for more information. |
| Interruption of interactive execution | If execution fails in the SQL Editor, resolve the problem that caused the failure. Then press F9 to resume execution from the failed statement. Do not ignore an error and resume execution after the failed statement. |
| Interruption of scheduled execution | If scheduled execution fails, resolve the problem that caused the failure. Then use the Restart function in the Scripts/Job Monitor to restart the script. This function can be used after an error, a cancellation, or a system shutdown. See Restart Scheduled Scripts for more information. |
| Monitor a switch | If the switch for a live reorganization requires user approval, check the Script/Job Monitor to see when a table is ready for the switch. The status of its script is “Waiting for approval”. |
| Cancellation and a switch | Although you can cancel reorganization scripts that are run on a scheduled basis, do not cancel a live reorganization script during the switch stage for any of the tables it contains. |
| Workaround when you cannot store a script | If you cannot store or save a script, a temporary file for the script is saved in the Reorg Scripts folder (under the Space Manager client folder). Once you can store or save the script, open its temporary file in the SQL Editor. Then save or store the file as a script. |
A Space Audit is Performed at Run Time
At script run time, Space Manager audits target tablespaces to see if they still have enough free space for a reorganization. This is a follow-up to the audit done before a reorganization script is generated. See Review Space Usage for more information.
Audit information is displayed near the beginning of a script in a remark that starts with “Auditing reorganization plan”. For each target tablespace involved in a reorganization, the information includes net movement and peak movement:
- Net Movement is how much data a tablespace is expected to contain after reorganization compared to how much it contained before reorganization. (It corresponds to the Starting Free Space value minus the Ending Free Space value displayed in the Review Space Usage window in Reorg Manager.)
- Peak Movement is the maximum amount of data to be moved into or out of a tablespace during reorganization. Peak Movement can sometimes be greater than Net Movement for a tablespace. This happens when a tablespace is used on an interim basis or when one or more objects are moved out of a tablespace.