QSA creates several objects for temporary use during a live reorganization. These include the collection table used for storing live transactions and the table used for storing posting statistics. One set of objects is created for each table in a live reorganization script. A table’s object ID is appended to the names of most LiveReorg objects.
Temporary LiveReorg objects are used in addition to QSA’s permanent objects. The names of temporary objects begin with QUEST_QSA_LW%. The names of permanent objects begin with QUEST_QSA_% or QUEST_EXEC_%.
All temporary LiveReorg objects are created in the LiveReorg tablespace. This is specified with QSA’s LW_TABLESPACE parameter. (If a ROWID index is used as the posting index, it is also created in this tablespace.) QSA’s permanent objects are stored in another tablespace. This is specified during first-time installation of the agent.
LiveReorg objects are created at the beginning of a live reorganization. Most are dropped from the LiveReorg tablespace after the switch from original table to reorganized copy table. If the T-Lock switch is used for a table with a LOB, the materialized view log and trigger created for that table are dropped right before the switch.
Important In most cases, you should not drop or change objects used by QSA. If a live reorganization script fails, leave LiveReorg objects in place while you resolve the problem that caused the failure. The tables will be used when the script is restarted.
A Look at the LiveReorg Tablespace
This section provides a list of the temporary LiveReorg objects that are created by QSA. It also identifies two permanent tables that are used by the agent during live reorganizations:
QUEST_QSA_ERR—This table is used for recording errors that occur during execution of collection triggers. (This is a permanent QSA object.)
Note The “LW” displayed in the names of LiveReorg objects stands for “lightweight”. The collection and posting processes associated with these objects are lightweight. All are designed to have low overhead and minimal impact.
A Look at the LiveReorg Tablespace
The tablespace used for LiveReorg objects is specified with QSA’s LW_TABLESPACE parameter. The LiveReorg tablespace must be specified when you install the agent. It can be changed at any time from the QSA Installer (through the Space Manager client) or from the QUEST_EXEC_PARAMETER table. See QSA Server Agent Parameters for more information.
If you try to exit from the QSA Installer without specifying a LiveReorg tablespace, a “Required parameter is not set” message displays. If you delete the parameter or its value from the QUEST_EXEC_PARAMETER table, live reorganizations fail with this error: “QSA-20391: ORA-NNNNN during internal SQL”.
It is recommended that you create or reserve a tablespace for exclusive use by LiveReorg objects. Dedicating a tablespace to LiveReorg objects ensures that the space they need will not be used by other objects. If you create a LiveReorg tablespace before installing the agent, that tablespace will be available for selection during the installation process.
Note LiveReorg tablespace must have a standard name, one that does not require double quotes.
To learn how to modify the LW_TABLESPACE parameter, see Specify Parameters for the QSA Server Agent.
The tablespace used for LiveReorg objects must have sufficient space for creating these objects. It must also have sufficient space for the objects to grow during a live reorganization.
At a minimum, the LiveReorg tablespace should have a datafile that is at least 50 megabytes in size. The datafile should contain at least 20 megabytes of contiguous free space.
How much additional space is needed as a live reorganization proceeds depends on row length for the collection table, total size of the columns in the posting index, and number of live transactions made against the original table during creation of the copy table. If additional space is needed by LiveReorg objects, it is allocated in five-megabyte extents.
Tip To see how much free space is available in the LiveReorg tablespace, select it in Space Manager’s Explorer. Then check Freespace fields at the top of the Explorer.
If the table being reorganized contains a LOB column, the LiveReorg tablespace needs additional space for the materialized view log that is created for the table. The log doubles the amount of free space required.
The materialized view log is used in conjunction with an AFTER INSERT trigger to collect direct updates to the LOB. As live transactions are made against the original table, rows for LOB updates are committed and then inserted into the log.
To keep the log from running out of space as rows are inserted, QSA periodically deletes all rows from the log. It does this using a process that runs every five seconds. If a row cannot be inserted in the log, Oracle returns the following error to your application: “ORA-12096: Error in materialized view log on “string”.”string”. See What to Do If a Materialized View Log Error Occurs for more information.
Prior to script generation, Space Manager checks to see whether the LiveReorg tablespace has enough space for creating LiveReorg objects. If a table has a LOB column, Space Manager also checks to see whether the tablespace has enough space for creating a materialized view log on the table. A space check of the LiveReorg tablespace is made along with space checks of target tablespaces. See Review Space Usage for more information.
If the LiveReorg tablespace does not have sufficient space for creating LiveReorg objects, warnings are displayed in Reorg Manager’s Review Space Usage window or the Partitioning Wizard’s Space Usage Summary tab. In addition, a message may be raised. To provide the free space needed, you can use Tablespace Properties to add datafiles or increase datafile size. You can leave Reorg Manager or the Partitioning Wizard open while you do this.
If you do not provide the space needed for creating LiveReorg objects, a live reorganization will fail with this error: “QSA-20394: Could not create temporary QSA object in <LW_TABLESPACE>”. If the LiveReorg tablespace does not have space for a materialized view log, your application will receive the ORA-12096 error. However, reorganization will not fail.
Note Space checks consider the space needed for creating LiveReorg objects. They do not consider additional space that might be needed for object growth. If a live reorganization fails because a LiveReorg object cannot grow, a collection trigger error is displayed in the script log.
During script generation, Space Manager checks to see whether the owners of tables with LOBs have quota for the LiveReorg tablespace. This is needed for creating the materialized view log used in collecting direct UPDATEs to LOBs.
If an owner does not have quota, Space Manager raises a warning right after generating a script. You can avoid the warning and automatically grant quota by leaving the Script Quota changes scripting option selected (it is selected by default). You can ensure that a script will not fail due to insufficient quota by selecting the Grant unlimited quota scripting option. Quota that did not exist prior to reorganization is revoked afterward.
To estimate how much space will be needed to create and grow LiveReorg objects during a live reorganization, use the following steps:
Some LiveReorg objects are sized with storage values specified by QSA. These objects are the collection table and the table that stores changes for LONG columns.
Other LiveReorg objects are sized with default storage values for the LiveReorg tablespace. These objects are the statistics table, the index on the collection table, and the materialized view log that is created for tables with LOB columns.
Default storage values for the LiveReorg tablespace should be set as follows:
| INITIAL | 1 MB |
| NEXT | 1 MB |
| MAXEXTENTS | UNLIMITED |
| PCTINCREASE | 0 |
| INITIAL | 1 MB |
| NEXT | 1 MB |
| MINIMUM EXTENT size | 1 MB |
| MAXEXTENTS | UNLIMITED |
| PCTINCREASE | 0 |
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
