Chat now with support
Chat with Support

Space Manager with LiveReorg 9.0 - User Guide

Smart Start for Space Manager Collect Statistics Profile DML Activity Manage Storage Reorganize Objects with Reorg Manager Partition Tables with the Partitioning Wizard Run and Monitor Scripts Run Reports Options Troubleshooting Administration Functions Appendix About Us

Select a Reorganization Method

The Reorg Manager provides two methods of reorganizing objects: Live Reorganization and Standard Reorganization. This topic provides information to help you select a reorganization method when using the Reorg Manager.

The method you select is used for all tables in a reorganization script.

Note The Partitioning Wizard automatically uses the live method.

See the following topics for additional information about selecting a reorganization method:

Standard Reorganization

Standard reorganization is an offline reorganization method. This means that the objects being reorganized are not available and should not be accessed during a standard reorganization. DML or DDL activity should not be performed against these objects by users or applications. If either type of activity is performed, data loss can occur.

Advantages of Standard Reorganization

One of the main advantages of standard reorganization with Reorg Manager is that it supports more object types, datatypes, and options than any other method.

Live Reorganization

The Live Reorganization option is enabled when LiveReorg is licensed and QSA is installed, current, and running for a database. Options for the online switch are enabled when Oracle’s Partitioning Option is installed for a database.

Note: Oracle’s Partitioning Option must be installed and licensed to use the Online switch.

Live reorganization with the Reorg Manager is designed to resolve space-use problems with minimum impact on data availability. When tables are reorganized using this method, applications can remain online and users can continue activity against the tables. Data remains available for Data Manipulation Language (DML) statements, including INSERTS, UPDATES, and DELETES.

When the online switch is used for a live reorganization, tables remain available throughout the entire reorganization, including the switch from original table to reorganized copy table. When the T-Lock switch is used, original and copy tables are locked very briefly for the switch.

Note: Before you can perform live reorganizations with Space Manager, the LiveReorg option must be licensed. In addition, the QSA Server Agent must be installed, current, and running.

 

Smart Start for Standard Reorganizations

This Smart Start outlines the workflow for standard reorganizations with Reorg Manager.

Important: Before you run a standard reorganization script, stop DML and DDL activity against the objects being reorganized. DML activity should be suspended until the script completes successfully.

  1. Run the Find Long LONGs procedure in order to populate the Find Long LONGs table.

    Reorg Manager checks this table for the size of LONG and LONG RAW columns. The size of these columns determines which data movement methods can be used to reorganize the column's table. See Use the Find Long LONGs Package for more information.

  2. Identify Objects That Need to Be Reorganized.

    Use the Reorg Need column in the Explorer to identify which objects need to be reorganized for each tablespace or each owner. Predicted Reorganization Benefit reports identify the benefits of reorganizing tables or indexes. See Identify Objects That Need to Be Reorganized for more information.

  3. Select the object or objects for reorganization in the Explorer and launch the Reorg Manager. See Launch Reorg Manager for more information. For help selecting which reorganization method to use, see Select a Reorganization Method for Reorg Manager.
  4. Select Target Tablespaces. See Select Global Reorganization Options for more information.

    Tip: You can select one tablespace for tables and one for indexes. This allows you to locate tables and their indexes in different tablespaces for better I/O balancing.

  5. Select script options in the Scripting Options page.

    These options let you increase reorganization speed with Oracle’s NOLOGGING mode and two types of parallel processing. See Select Scripting Options for more information.

  6. Select a Partition Reorganization Method.

    If the reorganization includes two or more partitions or subpartitions from the same parent object, the Partition Reorg Method dialog displays. The method selected determines whether partitions or subpartitions are reorganized individually or as part of an entire partitioned object. See Select a Partition Reorganization Method for more information.

  7. Set Storage Strategies.

    Define a global storage strategy in the Storage Strategy window. Based on your extent strategy, values for INITIAL, NEXT, and PCTINCREASE are set automatically for all objects. You can override global values for individual objects in the Customize Object Allocations window. See Set Storage Strategies for more information.

    Note: The window is only displayed when the System tablespace is data dictionary managed. For objects in locally-managed tablespaces, tablespace extent sizes and storage values are used by default.

  8. Customize Object Allocations.

    Adjust storage for individual objects in the Customize Object Allocations window. If needed, you can override extent values set on a global basis. You can also override global settings for target tablespace and sorted reorganization. See Customize Object Allocations for more information.

  9. Review Space Usage.

    Check the Space Usage Summary tab to see if target tablespaces and the LiveReorg tablespace have enough free space for a reorganization. This window identifies tablespaces that do not have enough free space overall or enough contiguous free space. To provide free space, select different target tablespaces or adjust storage values. To provide space in the LiveReorg tablespace, add datafiles or increase datafile size with Tablespace Properties. See Review Space Usage for more information.

  10. Generate and Run the Reorganization Script.

    Generate a reorganization script and select an execution option in the Schedule Script Execution window. See Schedule Script Execution in Wizards for more information. The script can be run immediately from the SQL Editor or on a scheduled basis from the database server. Scheduled execution is performed by the QSA Server Agent. Scripts that use FastCopy or parallel processing by QSA must be run on a scheduled basis. See About Running and Scheduling Scripts for more information.

    Tip: You can save Reorg Manager settings as a Reorg Plan to edit and reuse later. See Save Reorg Manager Settings for more information. You can also use saved settings to automate reorganization script execution. See About Scheduling a Recurring Reorganization for more information.

  11. Monitor Script Execution.

    Monitor script execution from the Scripts/Job Monitor or the SQL Editor. Use the Scripts/Job Monitor for scripts that are run on a scheduled basis. See Using Job Monitor to Monitor Scripts for more information.

 

Smart Start for Live Reorganizations

This Smart Start outlines the workflow for live reorganizations with Reorg Manager.

Note: Space Manager provides the best Reorg Need and Wasted space information when statistics are current in the Repository. Be sure to run the Repository Update job on regular or nightly basis (see Change Repository Update Schedule).

  1. Run the Find Long LONGs procedure in order to populate the Find Long LONGs table.

    Reorg Manager checks this table for the size of LONG and LONG RAW columns. The size of these columns determines which data movement methods can be used to reorganize the column's table. See Use the Find Long LONGs Package for more information.

  2. Identify Objects That Need to Be Reorganized.

    Use the Reorg Need column in the Explorer to identify which objects need to be reorganized for each tablespace or each owner. Predicted Reorganization Benefit reports identify the benefits of reorganizing tables or indexes. See Identify Objects That Need to Be Reorganized for more information.

  3. Select the object or objects for reorganization in the Explorer and launch the Reorg Manager. See Launch Reorg Manager for more information. For help selecting which reorganization method to use, see Select a Reorganization Method for Reorg Manager.
  4. Select the switch mode to use.

    The switch mode determines how the switch from the original table to the reorganized copy table is made. You can select to use a combination of online switch and T-Lock switch. Or you can select to use the online switch only, which excludes unsupported tables. See Select Global Reorganization Options for more information.

  5. Select Target Tablespaces. See Select Global Reorganization Options for more information.

    Tip: You can select one tablespace for tables and one for indexes. This allows you to locate tables and their indexes in different tablespaces for better I/O balancing.

  6. Select script options in the Scripting Options page.

    These options let you increase reorganization speed with Oracle’s NOLOGGING mode and two types of parallel processing. See Select Scripting Options for more information.

    Options that are unique to live reorganizations determine how the switch begins: automatically, upon user approval, or in a time window. They also determine whether original tables are dropped. See More About Scripting Options (LiveReorg) for more information.

  7. Select a Partition Reorganization Method.

    If the reorganization includes two or more partitions or subpartitions from the same parent object, the Partition Reorg Method dialog displays. The method selected determines whether partitions or subpartitions are reorganized individually or as part of an entire partitioned object. See Select a Partition Reorganization Method for more information.

  8. Set Storage Strategies.

    Define a global storage strategy in the Storage Strategy window. Based on your extent strategy, values for INITIAL, NEXT, and PCTINCREASE are set automatically for all objects. You can override global values for individual objects in the Customize Object Allocations window. See Set Storage Strategies for more information.

    Note: The window is only displayed when the System tablespace is data dictionary managed. For objects in locally-managed tablespaces, tablespace extent sizes and storage values are used by default.

  9. Customize Object Allocations.

    Adjust storage for individual objects in the Customize Object Allocations window. If needed, you can override extent values set on a global basis. You can also override global settings for target tablespace and sorted reorganization. See Customize Object Allocations for more information.

  10. Review Space Usage.

    Check the Space Usage Summary tab to see if target tablespaces and the LiveReorg tablespace have enough free space for a reorganization. This window identifies tablespaces that do not have enough free space overall or enough contiguous free space. To provide free space, select different target tablespaces or adjust storage values. To provide space in the LiveReorg tablespace, add datafiles or increase datafile size with Tablespace Properties. See Review Space Usage for more information.

  11. Choose a Scheduling Method. In a LiveReorg, you can schedule either script execution or script generation. See Choose a Scheduling Method (LiveReorg) for more information.
  12. Generate and Run the Reorganization Script.

    Generate a reorganization script and select an execution option in the Schedule Script Execution window. See Schedule Script Execution in Wizards for more information. The script can be run immediately from the SQL Editor or on a scheduled basis from the database server. Scheduled execution is performed by the QSA Server Agent. Scripts that use FastCopy or parallel processing by QSA must be run on a scheduled basis. See About Running and Scheduling Scripts for more information.

    Tip: You can save Reorg Manager settings as a Reorg Plan to edit and reuse later. See Save Reorg Manager Settings for more information. You can also use saved settings to automate reorganization script execution. See About Scheduling a Recurring Reorganization for more information.

    CAUTION: Reorganization commands must not be modified or removed from a script. If they are changed in any way, a reorganization can fail or unpredictable results can occur. Most commands begin with QUEST_EXEC.

  13. Monitor Live Reorganizations.

    Monitor script execution from the Scripts/Job Monitor and its Live Reorg Detail pane. See Monitor Live Reorganizations for more information.

    • Scripts/Job Monitor—This window displays execution status and executing statements.
    • Live Reorg Detail—This pane displays activity statistics for live transactions during the first three stages of a live reorganization. It also identifies which stages are complete. You can use the statistics to estimate when a table will be ready for the switch.

      Tip: If the switch option is Upon User Approval, check the Scripts/Job Monitor to see when each table in a script is ready for approval. “Waiting for approval” is displayed in the Status Message column (Detail pane).

  14. Approve the Switch.

    When a table is ready for the switch to be approved, right-click its script in the Scripts/Job Monitor (Detail pane) and select Approve Switch. The switch from original table to reorganized copy table is made and script execution continues. Monitor execution to see when the next table in the script is ready for approval. See Approve the Switch for more information.

 

Launch Reorg Manager

You can launch the Reorg Manager and the Partitioning Wizard from multiple points in the interface. Select one or more objects to reorganize, then launch the Reorg Manager or Partitioning Wizard.

Review the following notes about selecting objects.

  • When you select objects for the Reorg Manager or Partitioning Wizard, their associated objects are added to the object list. These objects include indexes, LOBs, and IOT overflows. IOTs (index organized tables) are replaced with IOT indexes.
  • In most cases, related objects descend from their base objects in a hierarchical view.
  • Partitions and subpartitions can be selected from all launch points. When a partition/subpartition is selected in the Partitioning Wizard, the entire partitioned object is added to the object list.

How to Launch the Reorg Manager

You can launch the Reorg Manager from several points in the Space Manager workspace: from the Explorer, from the Advanced Search window, and from certain reports. You can select a single object or multiple objects to reorganize simultaneously (in most cases).

Step 1: Select objects to reorganize

  • After identifying the object (or objects) in need of reorganization (see Identify Objects That Need to Be Reorganized), select the object/objects from one of the following areas in the Space Manager workspace:

    Selection method Description
    Select objects from the Explorer window

    Use the following techniques to find and select objects in the Explorer window:

    • One or more objects from the Top Issues list for the selected database.
    • One or more objects in a database. Use incremental Search and Group By fields to filter objects. Or use the Advanced Search window to find objects using multiple criteria.
    • One or more objects in one tablespace. Select from the Segment grid.
    • One or more objects belonging to one owner. Select from the object grid.
    • One or more objects in a datafile. Use the Datafile Map to find Segments with blocking extents.
    Select objects from a report

    Select an object from one of the following report types:

    • Reorganization Reports
    • Reorganization Candidates Reports

Step 2: Launch the Reorg Manager

  1. Use one of the previous methods to select one or more objects in the Explorer window or in a report.
  2. Right-click the object/objects and select Reorg Manager.
  3. Where applicable, select LiveReorg or Standard Reorg.
  4. The Reorg Manager opens to the Select Target Tablespaces page. See Select Global Reorganization Options for next steps.

Note: Before you can perform live reorganizations with Space Manager, the LiveReorg option must be licensed. In addition, the QSA Server Agent must be installed, current, and running.

Objects to be Reorganized - Color-Coding

In the Objects to be Reorganized list, color is used to differentiate certain objects. Use the following descriptions to identify objects by text color.

Text Color

Description

Black

In most cases, object names are displayed in black when objects are selected by the user.

Blue

Object names are displayed in blue when objects are selected by Reorg Manager. The objects selected by Reorg Manager are those that are related to objects selected by the user. Related objects include indexes and LOBs. They are reorganized along with their base objects.

Grey

Object names are displayed in gray when objects cannot be modified or when they are excluded from reorganization by you or Space Manager.

The names of LOB indexes, LOB index partitions, and LOB index subpartitions are always displayed in gray. This is to indicate that storage values cannot be set for these objects even though they are included in a reorganization along with their base tables, partitions, or subpartitions. (The LOB index clause is not displayed in reorganization scripts.)

Note: For all reorganization methods, you can exclude objects from the Customize Object Allocations window. For standard and live reorganization methods, you can also exclude objects in the  Select Partition Reorganization Method window.

  

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating