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:
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.
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 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.
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.
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.
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.
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.
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).
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.
Review the following information prior to running a reorganization script.
Review the following important considerations and best practices prior to running a reorganization script.
|Consideration / Best Practice
|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.
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: