Space Manager with LiveReorg 9.1 - User Guide

Defragment with an Interim Tablespace

The interim target tablespace option provides an alternative to reorganizing tables in their current tablespace. It allows you to reorganize them in a different tablespace, something that is useful when the current tablespace is low on free space. During a standard reorganization, this option defragments a tablespace when all objects are included.

First, all table data are copied from the original tablespace to copy tables in the interim tablespace or to space in the filesystem (depending on the data movement option in use). Then all original objects are dropped from the original tablespace at the same time. While data are stored in the interim tablespace or filesystem and the original tablespace is empty, all free space in the original is coalesced. Afterward, objects are recreated in the original tablespace and data is copied back to that tablespace. (Indexes are automatically dropped and recreated when an interim tablespace is used for their tables. However, the interim tablespace is not used for the indexes.)

To defragment with an interim tablespace

1. In the Object Explorer, select all objects in a tablespace for reorganization. (Hint: Use SHIFT+click to select all objects in the Segments grid for the selected tablespace.)
2. With the objects selected, right-click and select Reorg Manager | Standard Reorg.
3. In the Target Tablespaces section, select the interim tablespace option for tables. To do this, select Leave in Original Tablespace | Use Interim Tablespace. Also select the tablespace to use on an interim basis. Check the free space displayed for each tablespace. The one you select must have enough concurrent free space for all copy tables it will contain.
4. Under Indexes, select either target tablespace option for indexes. (Indexes are automatically dropped and recreated when an interim tablespace is used for their tables. However, the interim tablespace is not used for the indexes.)
5. Click Next to proceed to the Scripting Options windows and select options that control various aspects of a reorganization.
6. Proceed through remaining Reorg Manager windows. If the list of objects to reorganize includes individual partitions or subpartitions, a message asks if you want to drop and recreate the entire partitioned objects. Click Yes in response. If index partitions or subpartitions are reorganized individually, they remain in the tablespace during reorganization. As a result, it is not completely defragmented.
7. From the Schedule Script Execution window, select an execution option for the reorganization script. If the script uses only SQL, it can be run interactively from Space Manager’s SQL Editor or on a scheduled basis by the QSA Server Agent. If the script uses both SQL and FastCopy or only FastCopy, it must be run on a scheduled basis.

Related Topics

Defragment Tablespaces

Empty a Tablespace Before You Defragment

Empty a Tablespace Before You Defragment

In most cases, a tablespace is temporarily emptied during reorganization when all of its objects are included and the right combination of data movement and target tablespace options is selected. It is then defragmented.

However, under certain circumstances, objects may remain in a tablespace during a reorganization even when they are included on the object list. This happens when:

• The objects are cluster indexes.
• Index partitions and subpartitions are reorganized individually.
• The objects are not supported for reorganization with either FastCopy/DBMS_DataPump or SQL. An example of such an object is one that is not supported for FastCopy and also contains a LONG column that is too large for SQL.

When objects remain in a tablespace during reorganization, it cannot be completely defragmented.

There are workarounds for moving cluster indexes and index partitions and subpartitions out of a tablespace in order to defragment it. These are described in the sections that follow. However, there are no workarounds for objects that are not supported for either FastCopy/DBMS_DataPump or SQL. See What is Not Supported for Standard Reorganization for more information.

Workarounds for Cluster Indexes

If you want to defragment a tablespace that contains cluster indexes, the workaround for moving them out of the tablespace is to relocate them during reorganization. To do this, select a new tablespace for each cluster index in Reorg Manager’s Customize Object Allocations window.

Workarounds for Index Partitions and Subpartitions

If you want to defragment a tablespace that contains individual index partitions or subpartitions, the main workaround is to reorganize the entire partitioned index. The entire partitioned index can be reorganized as a single object no matter where its individual partitions or subpartitions are located in a database.

Normally, index partitions and subpartitions are not moved out of a tablespace when they are reorganized individually. However, two additional workarounds make it possible to reorganize index partitions and subpartitions individually and still defragment a tablespace:

• Workaround for local and global index (sub)partitions—For local and global index (sub)partitions that are being reorganized along with their table segments, one workaround is to relocate the index segments during reorg. To do this, first select the Exchange partition reorg method for the table segments in Reorg Manager’s Select Partition Reorganization Method window. Then select a new tablespace for each index partition or subpartition in the Customize Object Allocations window. You can move the index segments back to their current tablespace with a second reorg, if needed.

• Workaround for global index partitions—For global index partitions that are being reorganized without their table segments, an additional workaround is to use the Move/Rebuild partition reorg method. To do this, select the Move/Rebuild method for the global index partitions in Reorg Manager’s Select Partition Reorganization Method window. Then select a new or interim tablespace for each global index partition in the Customize Object Allocations window.

Related Topics

Defragment Tablespaces

Defragment with an Interim Tablespace

How Live Reorganizations Work

How Live Reorganizations Work

Live reorganizations are designed to restore tables to peak capacity with minimum impact on their availability. When tables are reorganized on a live basis, applications can remain online and users can continue activity against the tables. Data remains available for Data Manipulation Language (DML) statements, including INSERT, UPDATE, and DELETE statements.

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.

Each live reorganization is performed in a series of stages. During these stages, a copy of the table being reorganized is created. Data from the original table is reorganized as it is inserted into the copy table.

As the copy table takes shape, applications and users can make changes to data in the original table. All changes are captured by QSA, Space Manager’s script execution agent. The agent continually posts (copies) these changes to the copy table. Once both tables are completely in sync, the “switch” is made. At this point, the original table is replaced with its reorganized copy table, which can then be accessed as usual.

When the online switch is used, tables remain available throughout an entire live reorganization. When the T-Lock switch is used, the table is unavailable during the switch. Any application trying to access the table during the switch will get a database error. Be sure that your application will handle these errors appropriately. See Switch Mode for more information.

When multiple tables are included in a live reorganization script, if they are not partitioned, Space Manager uses a parallel query to reorganize tables. If the tables are partitioned, there is one switch per table, and reorganization of the next table in the script does not begin until all tasks have been performed for the table ahead of it.

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.