Space Manager provides two ways to save Reorg Manager settings: Save as Default or Saved Reorg Plan.
Saved Reorg Plan
Space Manager allows you to save the settings you select in the Reorg Manager as a Reorg Plan to reuse later. You can save the settings at any stage in the wizard. Saving Reorg Manager settings allows you to save the reorganization parameters selected on each page of the Reorg Manager wizard, as well as the Objects to be Reorganized list. This feature is also available in the Partitioning Wizard.
To use a saved Reorg Plan, open it from the File menu. The Reorg Manager then launches with the saved settings and object list applied. You can have multiple saved Reorg Plans.
Tip: To save an unfinished reorg preparation in the Reorg Manager or Partitioning Wizard and continue it later, save it as a Reorg Plan.
Save As Default
You can also save the settings in the Reorg Manager or Partitioning Wizard to use as the default settings. Once you save the settings as default, Space Manager displays these settings each time you use the Reorg Manager or Partitioning Wizard.
To save a Reorg Plan
To open a saved Reorg Plan
To Save (settings) as Default
Tip: In addition to the two methods described in this topic, you can also specify some default options for the Partitioning Wizard in the Options dialog (Tools | Options | Partitioning Wizard). See Partitioning Wizard Defaults.
Use the View in Editor option in the Reorg Manager (or Partitioning Wizard) to run reorganization scripts interactively from the SQL Editor. As a script runs, you can monitor execution directly from the SQL Editor. You can also use the View in Editor option to open a script and review it before you schedule it.
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).
To run scripts from the Editor
To run the script in single-step mode (one statement at a time), click Single Step in the toolbar or press F8 from the first statement. Repeat for each statement in the script.
Note: If the Connection dialog displays, enter the user name and password of an Oracle user with the DBA role or privileges for Space Manager. Then click OK to connect to your database.
In the Reorg Manager, statistics collection options are specified on the Scripting Options page (Analysis section). These options allow you to automatically collect or restore statistics when a reorganization script is run.
See Collect Statistics for an overview of statistics collection in Space Manager.
The Reorg Manager provides the following options for statistics collection:
Restore original statistics in data dictionary for the CBO—This option lets you preserve existing statistics in the Oracle data dictionary instead of collecting statistics. It is designed for use when data-dictionary statistics are pre-set for Oracle’s cost-based optimizer.
Note: Restore functionality requires the DBMS_STATS package.
For information on how to specify statistics collection options in the Reorg Manager, see Select Scripting Options.
When you restore statistics during statistics collection, data dictionary statistics are copied before objects are reorganized. The statistics are restored to the data dictionary afterward. The backup and restore are performed using procedures from the DBMS_STATS package. Before a reorganization begins, the export_table_stats procedure is used to export existing statistics from the data dictionary to a backup table. This is called QUEST_SPC_REORGANIZATION_STATTAB. After statistics are collected, statistics in the backup table are imported into the data dictionary with import_table_stats. They are then deleted from the backup table with delete_table_stats.
Important: The DBMS_STATS procedures used by the restore option processes statistics needed by Oracle’s cost-based optimizer. They do not process statistics such as number of chained rows, average free space in data blocks, number of empty blocks, and average space in freelist blocks. As a result, these statistics are not preserved by the restore option. If they exist in the data dictionary before collection is performed with this option, they will not exist there afterward.