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 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Monitor Live Reorganizations.
Monitor script execution from the Scripts/Job Monitor and its Live Reorg Detail pane. See Monitor Live Reorganizations for more information.
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).
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.
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.
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:
|
| Select objects from a report |
Select an object from one of the following report types:
|
Step 2: Launch the Reorg Manager
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.
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. |
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
