RollbackToSP Procedure

This procedure discards all changes made after a specified savepoint in the workspace to all tables.

Syntax

DBMS_WM.RollbackToSP(
   workspace       IN VARCHAR2,
   savepoint_name  IN VARCHAR2
   [, auto_commit  IN BOOLEAN DEFAULT TRUE]);

Parameters

Table 70-34 RollbackToSP Procedure Parameters  

Parameter	Description
workspace	Name of the workspace. The name is case sensitive.
savepoint_name	Name of the savepoint to which to roll back changes. The name is case sensitive.
auto_commit	A boolean value (TRUE or FALSE). TRUE (the default) causes the operation to be executed as an autonomous regular transaction that will be committed when it finishes. FALSE causes the operation to be executed as part of the caller's open regular transaction (if one exists). If there is no open regular transaction, the operation is executed in a new regular transaction. In either case, the caller is responsible for committing the transaction.

Usage Notes

While this procedure is executing, the workspace is frozen in NO_ACCESS mode.

Contrast this procedure with RollbackWorkspace Procedure, which rolls back all changes made since the creation of the workspace.

You cannot roll back to a savepoint if any implicit savepoints have been created since the specified savepoint, unless you first merge or remove the descendant workspaces that caused the implicit savepoints to be created.

An exception is raised if one or more of the following apply:

• workspace does not exist.

• savepoint_name does not exist.

• One or more implicit savepoints have been created in workspace after savepoint_name, and the descendant workspaces that caused the implicit savepoints to be created still exist.

• You do not have the privilege to roll back workspace or any affected table.

• Any sessions are active in workspace.

Examples

The following example rolls back any changes made in the NEWWORKSPACE workspace to all tables since the creation of Savepoint1.

EXECUTE DBMS_WM.RollbackToSP ('NEWWORKSPACE', 'Savepoint1');