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

About Switch Modes

Switch mode (style) determines whether a table remains available for DML activity during the switch from original table to reorganized copy table. A table remains available when the online switch mode is used. A table is briefly unavailable when the T-Lock switch is used.

In addition to switch mode, you can select a switch option for a live reorganization:

  • Switch mode determines how the switch from original table to the reorganized copy table is made: online switch or T-Lock switch. The switch mode is selected in the first page of the Reorg Manager.
  • Switch option determines how the switch begins once a copy table is ready to replace its original table. The switch can begin automatically, upon user approval, or in a time window you specify. A switch option is selected in the Scripting Options page of the Reorg Manager.

More About the Online Switch

When the online switch mode is used, the table being reorganized remains available throughout a live reorganization, including the switch. Applications can remain online and users can continue DML transactions against the original table as it is replaced with its reorganized copy.

The original table remains available even if execution fails. All live transactions made while a script is interrupted are posted to the copy table when script execution resumes. After you resolve the problem that caused a failure, you can restart script execution using the Restart function in the Scripts/Job Monitor. However, you should not restart a script if an error prevents an online switch. See Restart for Live and Partitioning Scripts for more information.

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

Special Considerations

  • When the online switch is used for a live reorganization of a partitioned table, the table’s partitions or subpartitions are reorganized individually. A message alerts you to this after you select the Exclude tables requiring the T-Lock switch (online switch only) mode.
  • The online switch is not used with FastCopy or DBMS_DataPump. When the Exclude tables requiring the T-Lock switch (online switch only) mode is selected, all tables will be reorganized with SQL.
  • The Exclude tables requiring the T-Lock switch (online switch only) mode should not be selected when a ROWID posting index will be used for any of the tables in a script. See Posting Indexes for more information.
  • The Exclude tables requiring the T-Lock switch (online switch only) mode cannot be used for certain types of objects. See What is Not Supported for Live Reorganizations for more information.

More About the T-Lock Switch

When the T-Lock switch is used, the original table and its reorganized copy table are locked against changes during the switch. Trigger locks are used. Normally, the duration of the switch stage is very brief.

Although T-Locks prevent write access, they allow read-only access for most of the switch. When an entire table is being reorganized, read-only access is not available at the point when original and copy tables are renamed. At that point, Oracle places an exclusive lock on the original and copy tables. The duration of the lock is a few seconds. To see when objects are being renamed, check the Scripts/Job Monitor. This displays an ALTER TABLE RENAME statement as the rename takes place.

What Is Locked

When a table partition or subpartition is reorganized individually, a T-Lock is placed on its parent-partitioned table. The entire table is locked. A T-Lock is also placed on the copy object. When an entire table is reorganized, T-Locks are placed on both the original table and its copy table. T-Locks on original and copy tables remain in place throughout the switch stage.

In some cases, T-Locks are also placed on tables that have FOREIGN KEY relationships with the table being reorganized. These include tables that reference the original table and tables that are referenced by the original table. Any T-Locks on tables involved in FOREIGN KEY relationships last only as long as it takes to recreate constraints.

Space Manager locks all tables related to the reorg table before starting the switch. If Space Manager cannot lock all the tables, it removes the locks and aborts the script while still capturing updates. This allows you to restart the reorg script when there is less or no activity going against the tables.

At the end of the switch stage, T-Locks are removed and the reorganized table can be accessed. T-Locks are removed as follows:

  • Removal from tables—If a table was reorganized, the T-Lock is removed from the reorganized copy table. If the original table is dropped after the switch, the T-Lock is dropped along with it. If the original table is kept, the T-Lock remains in place. If you need to remove the T-Lock from the original table, contact Quest Software Technical Support (see About Us).
  • Removal from partitions and subpartitions—If a partition or subpartition was reorganized, the T-Lock is removed from its parent-partitioned table. At this point, the reorganized partition or subpartition is part of the original parent table.

What Happens When Locked Tables Are Accessed

If users or applications attempt to access a T-Locked table during the switch, one of two messages is displayed in the applications. Messages are displayed in the following circumstances:

  • If attempts are made to modify data in a table protected by a T-Lock, this QSA Server Agent message displays:

    QSA-20509: Live reorganization in progress

    The purpose of the message is to alert users that changes cannot be made at the current time. It does not indicate a problem with a reorganization.

  • If attempts are made to query a table protected by an exclusive lock, this Oracle error might display:

    ORA-00942: Table or view does not exist

    If SAP is the application in use, a “table unknown error” is displayed. An exclusive lock is applied by Oracle when a table is renamed during a T-Lock switch.

A message is not raised if a T-Lock switch is made when there is no activity against the tables being reorganized. You can control when a switch is made using scripting options that determine how the switch begins. The user approval option allows you to stop activity and then approve the switch. The time window option allows the switch to be made automatically at a time when the database is quiet. See More About Scripting Options (LiveReorg) for more information.


Change Approval Method

You can change the approval method (switch option) for online scripts. Approval method determines how the switch proceeds for a live reorganization script or a partitioning script.

Approval method can be changed for online scripts that are waiting to run or waiting to be restarted. A change in approval method applies to all tables in a script for which the switch has not yet been made.

For more information about approval methods (also known as Switch Options), see Select Scripting Options and More About Scripting Options (LiveReorg).

To change approval method

  1. In the Script/Job Monitor, do one of the following to change approval method:
    • Right-click a script group and select Change Approval Type.
    • Right-click a script group and select Properties. Then select the Script Attributes tab.
  2. Select one of the approval types.
  3. Click OK to save your changes and close the dialog.


Related Topics

Approve the Switch

When the switch option for a live reorganization script is Upon user approval, you must approve the switch for each table in the script. You can do this from the Scripts/Job Monitor or from its Live Reorg Detail pane using one of the following procedures.

To see if a table is waiting for the switch to be approved, check the Status Message column in Scripts/Job monitor. “Waiting for approval” is the status for a script when one of its tables is waiting for approval.

After you approve the switch for one table in a script, check to see when the next table is ready for the switch. How much time passes before approval is required depends on the size of the table being reorganized, the number and size of its indexes, and the level of activity against the table.

To approve the switch from the Scripts/Job Monitor

  1. Open the Scripts/Job monitor by selecting Manage | Script/Job Monitor.
  2. Check the Status Message column. “Waiting for approval” is displayed for each live reorganization script with a table that is ready for the switch.
  3. Right-click a live reorganization script and select Approve Switch.

To approve the switch from Live Reorganization Detail

  1. Open the Scripts/Job monitor by selecting Manage | Script/Job Monitor.
  2. Select a live reorganization script. Information on the script is displayed in the Live Reorganization Detail pane.
  3. When “Stage 3 (Waiting for approval)” is displayed for a table in the Live Reorg Detail pane, right-click anywhere in the pane and select Approve Switch.


Getting Started with Reorg Manager

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating