Locking problems with Live Reorg possibly extending switch times. (4354286)

During the execution of a Space manager script two brief exclusive locks are taken out against the table in order to obtain a synchronization point.

The two places in the job are at the start of the table copy and at the start of the switch.

The first lock ensures that the table is not structurally changing and that Oracle can provide a read consistent image of the table. The second is required to pause transaction processing whilst the switch occurs.

The locks are only held for a brief moment to get the sync point, and are then dropped.
This information applies for all copy methods and all switch options.

Obtaining these locks is something that requires some management by the DBA, as an exclusive lock request on a busy table may cause Oracle to queue subsequent lock requests behind the exclusive lock. This is an Oracle issue not a Space Manager issue The DBA must schedule these points in the task at a time when requesting an exclusive lock will not cause a backlog on the database.

The timing can be controlled as follows;
The table copy starts when the script is launched, so the timing of the first lock can be controlled by selecting when to launch the script. The switch starts once approval to switch has been granted, so the timing of this can be controlled by manually approving the switch. This approach requires busy tables to be identified and scripted individually. This is recommended for large busy tables.

Most OLTP systems have transactions that commit in a small timescale, unless batch jobs or large transactions are being executed. So normal OLTP transactions should not cause problems with exclusive locking, but longer batch or large transactions should be avoided.

The appropriate action to take if an issue is identified where the exclusive lock has not been achieved is to cancel the reorg job in the Job Monitor screen. The job can then be resumed at a time when the lock can be obtained.

Space Manager scripts should be run against tables when the application activity is low to reduce the impact on the application and increase the probability of a successful reorg, for example the demands of maintaining a read consistent image within Oracle.

Customers have asked if the locks can be removed from the product, but these locks ensure the validity of the data, so removing the locks would result in corruption of data.