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
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:
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.
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.
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 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.
Defragment with an Interim Tablespace
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.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
