Space Manager with LiveReorg 9.0 - User Guide

About Data Movement Methods

The data movement method determines how data is moved during a standard or live reorganization. Data is moved from original tables to reorganized copy tables using either SQL or FastCopy (or DBMS_DataPump).

Note: For Oracle database 12c or later, Space Manager uses DBMS_DataPump instead of FastCopy.

In previous versions of Space Manager (8.0.2 or earlier), the Reorg Manager allowed you to specify a data movement method. However, Space Manager is capable of determining the most efficient data movement method to use for each object you want to reorganize. So, beginning with Space Manager 8.1, the Reorg Manager now automatically selects the most appropriate data movement method (SQL or FastCopy/DBMS_DataPump) and includes it in the reorg script. SQL is used for tables that do not contain LONG or LONG RAW columns. FastCopy/DBMS_DataPump is used for supported tables that contain LONG or LONG RAW columns.

This method of allowing Space Manager to automatically script the most appropriate data movement method is the most inclusive. It allows all tables with LONG columns to be included in a reorganization regardless of the size of these columns. Either SQL or FastCopy/DBMS_DataPump can be used for LONGs up to 32,760 bytes, and FastCopy/DBMS_DataPump supports LONGs that are up to 2 GB in size. This method also allows tables that are not supported for FastCopy (or DBMS_DataPump) to be included in the reorganization.

Important: If a table is not supported for FastCopy or DBMS_DataPump and contains a LONG or LONG RAW column in which some rows contain data that is 32,760 bytes or larger, the table cannot be reorganized.

Using FsCopy and DataPump

Beginning with release 8.3, you can instruct Space Manager to use either FsCopy or DataPump as the data movement method, depending on your Oracle database version. FsCopy uses an import and export process to move data. It is one of the actions included in the Space Manager FastCopy data movement method. See Select Global Reorganization Options.

Selecting the Use FsCopy or Use DataPump option is recommended only for tables that would otherwise get a "snapshot too old" error during table copy.

Important: This option is not recommended for most types of reorganizations. Care should be taken when selecting this method. FsCopy and DataPump are not always the most efficient data movement methods and should be used only for large tables where table copy might cause Oracle errors.

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.

Data Movement Method for Related Objects

Indexes and other related objects are automatically reorganized along with their tables. SQL is always the data movement method used for indexes, even when FastCopy (or DBMS_DataPump) is used for their tables.

More About Scripting Options (LiveReorg)

Several scripting options are unique to live reorganizations. All are set from the Scripting Options page of Reorg Manager. These options determine how the switch from original table to reorganized copy table begins—automatically, upon user approval, or in a certain time window. They also determine whether original tables are dropped or saved after they are replaced with reorganized copy tables.

Set Switch Option

The switch option (approval method) selected for a live reorganization script is used for all tables, table partitions, and table subpartitions in the script. When multiple tables, partitions, or subpartitions are included, they are reorganized serially, one at a time. There is one switch per object.

How soon a table is ready for the switch after reorganization begins depends on the table’s size, the size and number of its indexes, and whether statistics are being collected. Recreating a large table and its indexes could take several hours or more.

Tip: After a live reorganization script has been stored and scheduled, you can select a different switch option (approval method), if needed. This is done through the Script/Job Monitor. The change must be made before script execution begins. See Change Approval Method for Online Scripts for more information.

Set the Automatic Switch Option

Select the Automatically option if you want the switch to be made automatically, as soon as a copy table is ready. When multiple tables are included in one reorganization script, the switch is made automatically for each table in turn.

The automatic switch option is a good choice when the online switch style will be used for all tables. By allowing the switch to occur automatically, you can avoid unnecessary delays that might occur if another switch option were used. You also avoid the need to be on hand to approve the switch.

Set the User Approval Switch Option

Select the Upon user approval option if you want the switch to made after a user approves the switch.

Approval should be given as soon as possible after a table is ready for the switch. Until approval is given, QSA keeps the original table in sync with the copy table.

When multiple tables are included in a reorganization script, multiple approvals must be given. How much time passes before each approval is required depends on the size of each table being reorganized, the size and number of its indexes, and whether statistics are being collected.

Approval is given from the Script/Job Monitor. When any of the tables in a reorganization script are ready for the switch, “Waiting for approval” is the status of the script. See Approve the Switch for more information.

The user approval switch option is a good choice when the T-Lock switch style will be used for one or more tables. It gives you a chance to stop activity against a T-Locked table prior to the switch. This allows you to avoid the message that displays when attempts are made to modify data in a T-Locked table. The purpose of the message (“QSA-20509: Live reorganization in progress”) is to alert users that changes cannot be made at the current time. It does not indicate a problem.

Set the Time Window Switch Option

Select the Between date/time option if you want the switch to be made within a time window. Also specify a start date and time and end date and time for the window. The default start time is the current date and time. The default end time is one hour later. The switch is made automatically for all tables that are ready for the switch during the time window specified.

The start of the switch window should be coordinated with the start of script execution. Ideally, the switch window should be timed to start before the first table is ready for the switch. To estimate when the first table will be ready, consider its size, the size and number of its indexes, and whether statistics are being collected. Recreating a large table and its indexes could take several hours or more.

The duration of the switch window should be long enough to accommodate the switch for all tables in a script:

• If the window starts after the first table is ready for the switch, QSA continues posting live transactions to its reorganized copy table. Once the window starts, the switch is made for the first table. It is then made for all subsequent tables as soon as they are ready. The status for a script that is waiting for a time window is “Waiting until Day/Date/Time”.
• If the window ends before the switch is made for all tables in a script, execution aborts with this message: “QSA-20502: Target completion date/time expired.” This happens after 20 “retry operation” attempts are made.

The time-window option provides an alternative to the user-approval option. Where the user-approval option gives you a chance to stop activity against a table and then approve the switch, the time-window option allows the switch to proceed automatically at a time when activity is light.

Tip: You can approve the switch instead of waiting for a time window to start. The status for a script that is waiting for a time window to start is “Waiting until Day/Date/Time”. See Approve the Switch for more information.

Making the switch when the database is quiet allows you to avoid the message that displays when attempts are made to modify data in a T-Locked table. The purpose of the message (“QSA-20509: Live reorganization in progress”) is to alert users that changes cannot be made at the current time. It does not indicate a problem.

Set the Drop Original Table Option

You can control whether original objects are dropped or saved after a reorganization using the Drop original table after successful reorganization check box.

This check box is selected by default so that original tables and indexes are dropped after they are replaced with their reorganized copies. Original objects are also purged from the Recycle Bin.

If you want to save original tables and indexes, clear the check box. Original objects are preserved in their original tablespaces under modified names. (When a table is reorganized with the T-Lock switch, unique indexes are kept and indexes that enforce a constraint are not saved.)

Dropping original objects frees up space. Saving original objects provides a backup of table data up to the point where the switch completes.

Note: Saving original tables does not save objects created by QSA. For example, if a ROWID index is created for use as the posting index, the index is dropped after its table is reorganized.

Names Used for Saved Original Objects

The following chart displays the names used for original objects that are saved after a live reorganization. Each name includes the ID of an original table, partition, or subpartition.

Select a Partition Reorganization Method

When two or more partitions or subpartitions from the same table or index are included in a reorganization, the Partition Reorg Method dialog displays. This dialog allows you to select a partition reorganization method, i.e., whether partitions or subpartitions are reorganized individually or as part of an entire partitioned object.

The Partition Reorg Method dialog asks if you want to drop and recreate the entire partitioned object. This gives you the opportunity to reorganize the entire parent object which might be faster than reorganizing its partitions or subpartitions individually.

To select a partition reorganization method

When the Partition Reorg Method dialog displays, do one of the following.

• Click Yes to reorganize the entire parent object.

  All partitions or subpartitions in an object are reorganized as a single object. If this method is selected for a table, all indexes on the table are also dropped and recreated. The parent and all related objects are retrieved and a red arrow displays beside the parent object in the Objects to be Reorganized list.

• Click No to reorganize the partitions or subpartitions individually. Green arrows display beside the partitions/subpartitions in the Objects to be Reorganized list.

Note that if there are multiple parent objects listed in the Partition Reorg Method dialog, the partition reorg method you select is applied to all. To select a different partition reorg method for one or more of the parent objects listed, reorganize them separately.

Note: When selecting two or more partitions/subpartitions (from the same object) to reorganize in a LiveReorg, if you select the Online switch only mode, table partitions and subpartitions are reorganized individually.

Arrows Indicate Objects to be Reorganized

After you make your selection in the Partition Reorg Method dialog, arrows display in the Objects to Reorganize list to indicate how partitions are to be reorganized (individually or with the parent).

• Green arrows display beside partitions and subpartitions that will be reorganized individually.

  

• Red arrows display beside partitioned objects that will be reorganized as a whole.

  

Set Storage Strategies

After you select scripting options or a partition reorganization method, you can define a global storage strategy in the Storage Strategy window. Options in this window let you adjust your current strategy in order to accommodate future growth or use space more efficiently.

Note: This window only displays if you have dictionary managed tablespaces. For objects in locally-managed tablespaces, tablespace extent sizes and storage values are used by default.

Defining extent allocation on a global basis saves you time when the same strategy can be used for all or most objects in a reorganization. If needed, you can override global extent values for individual objects in the Customize Object Allocations window. See Customize Object Allocations for more information.

As you define a strategy, check the Estimated Extent Allocation Summary. This displays total current allocation for all objects in kilobytes and number of extents. It also displays estimates of what the new allocation will be after reorganization. Post-reorganization estimates in Allocation and Extents fields are recalculated each time you adjust a strategy.

Review the following for additional information:

Default Maximum Extent Size

The maximum extent size allowed by Space Manager is two gigabytes minus 16 kilobytes (2097136K). However, the default value for maximum extent size can be changed. For example, the default can be decreased if your system limits database file size. To change the maximum extent size

Insert a row for maximum_extent_size in the QUEST_SPC_PARAMETER table and specify your maximum in the VALUE column. If you are decreasing the 2G maximum, value can be expressed as K, M, or G.