Cross-instance rollback segments
Date: Mon, 22 Feb 1999 13:56:34 -0800
Message-ID: <36D1D292.2A34_at_artemis.fraser-williams.com>
Our application has to copy a large amount of data ('00s Mb) in one transaction (to be able to rollback the whole operation on error) from one schema (source) to another schema (target). The application is written in PL/SQL and is stored on and run from the source.
To limit the impact on other users we have a dedicated, large capacity rollback segment on the _source_ instance called BIG_RS. The first command of the PL/SQL code is to use:
SET TRANSACTION USE ROLLBACK SEGMENT BIG_RS This works okay if we are copying between two schemas within the same instance. However, if we want to copy data to a target schema on a different instance (which doesn't have BIG_RS), the rolled back data (i.e. the newly copied data on the target) will be stored on a random system rollback segment on the target.
As this system rollback segment is a 'standard' size it quickly runs out of extents and fails.
We don't mind creating another BIG_RS on the target instance, but is there any way to specify which rollback segment to use, when running the SET TRANSACTION command on the source but selecting a rollback segment for the target?
Or, does anyone have any other suggestions on how this can be done?
The PL/SQL code may not be stored on the target. Received on Mon Feb 22 1999 - 22:56:34 CET