Space Manager with LiveReorg 9.1 - User Guide

Save Settings As Default

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

1. You can save a Reorg Plan by clicking the Save Reorg Plan button found in the following locations:
   • On any page of the Reorg Manager (LiveRorg or Standard Reorg).
   • On the Schedule Script Generation page of the Reorg Manager (LiveReorg only). A Reorg Plan is used when you schedule script generation. See Schedule Script Generation for more information.
   • On any page of the Partitioning Wizard.
2. Enter a name for the file. The Reorg Plan is saved as an XML file.

To open a saved Reorg Plan

1. Select File | Open Saved Reorg Plan.
2. Select a previously-saved Reorg Plan (.xml). Click Open.
   • If the settings were saved from the Reorg Manager, the Reorg Manager opens with the saved settings applied automatically.
   • If the settings were saved from the Partitioning Wizard, the Partitioning Wizard opens with the saved settings applied automatically.
3. You can use the settings "as is" or modify them further before saving, executing, or scheduling the script.
4. To edit a saved Reorg Plan, open the plan (File | Open Saved Reorg Plan), modify the settings, then save the plan using the same file name.

To Save (settings) as Default

1. In any applicable page of the Reorg Manager or Partitioning Wizard, click Save As Default.
2. The current settings are saved and will be applied each time you launch the Reorg Manager or Partitioning Wizard from anywhere except a saved Reorg Plan. Saved settings in the Reorg Plan override the default settings.

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.

Run Scripts from the Editor

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

1. Select View in Editor in the schedule window of Reorg Manager or the Partitioning Wizard. The script is displayed in the SQL Editor.
2. Run the script from the SQL Editor in continuous mode or single-step mode:
   • To run the entire script in continuous mode, click Run in the toolbar or press F9.

   • 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.

3. As the script is executed, the SQL Editor scrolls through script statements. The current statement is highlighted. Check the status bar at the bottom of the window. If a statement is executed successfully, processing time is displayed. If an error occurs, an error message is displayed.
4. When script execution is finished, an execution log for all script statements displays in the Spool tab. View this to see script start and stop times, any errors that occurred, and the output of all executed statements.

Related Topics

About Running and Scheduling Scripts

Schedule Scripts

Schedule Script Execution in Wizards

More Options for Partitioning Wizard

About Statistics Collection Options

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:

• Collect statistics for the CBO (cost-based optimizer)—This option lets you collect post-reorganization statistics.

• 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.

How the Restore Option Works During Collection

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.