Space Manager with LiveReorg 9.1 - User Guide

Set the Posting Parameter

A QSA parameter controls whether the “all columns” posting method or ROWID posting indexes are used for tables without selective indexes that contain LOBs, LONGs, XMLTypes, or user-defined datatypes (object types, REFs, and VARRAYs).

The parameter is called ALLOW_LONG_WO_UNIQUE and is specified in the VALUE column of the QUEST_EXEC_PARAMETER table:

• When the parameter is set to NO, a ROWID posting index is used for tables that contain LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs).
• When the parameter is set to YES, the “all columns” posting method is used for tables that contain LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs). It is used in conjunction with a non-selective posting index or no index. The nonselective index can be a unique index with all columns nullable or a non-unique index without a unique constraint.

By default, ALLOW_LONG_WO_UNIQUE parameter is set to NO. It can be changed while the agent is running and the database is online. It should only be changed to YES if you are certain that rows can be identified uniquely based on columns that do not contain LOBs, LONGs, XMLTypes, and user-defined datatypes (object types, REFs, and VARRAYs).

Related Topics

Posting Indexes

How Space Manager Chooses a Posting Index

Prompts to Validate Constraints

All Columns Posting Method

ROWID Posting Indexes

Collection Triggers and the Collection table

Collection trigger has failed (20777)

Trigger Oracle error is - 103

Error msg: Failed LW collector trigger

The Posting Procedure and OCI Calls

To post live transactions to reorganized copy tables, QSA uses a PL/SQL stored procedure and Oracle Call Interface (OCI) calls:

• Stored procedure—This is used to post live transactions for all columns except LONG columns. The procedure is called QUEST_QSA_LWRP<obj#>. The last part of the procedure name is the object ID of the original table. The stored procedure retrieves information on transactions from the QUEST_QSA_LWDATA<obj#> collection table.
• OCI calls—These are used to post live transactions for LONG and LONG RAW columns. OCI calls retrieve information on transactions from the QUEST_QSA_LWLONG<obj#> table.

When a table contains a LONG column, changes to non-LONG columns are posted first with the stored procedure. Any changes needed for the LONG column are posted afterward with OCI calls.

Each execution of the stored procedure is called a “posting session”. If OCI calls are executed after the stored procedure is run, they are considered to be part of the posting session.

Both the stored procedure and OCI calls retrieve transactions in chronological order (oldest transactions first).

Note The reason OCI calls are used for LONG columns is to ensure that data of any size is successfully posted. LONG values larger than 32,760 bytes are not supported for PL/SQL. If a PL/SQL stored procedure were used to post a non-supported LONG value, data would be truncated.

LiveReorg Objects and Their Tablespace