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:
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.
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.
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:
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.
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
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
To approve the switch from Live Reorganization Detail
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center
