ALTER SESSION

Use the ALTER SESSION command to abort the active transaction in a session, to set the priority of a session, or to add a tag to or remove a tag from a session.

Syntax

Syntax for aborting a transaction in a session:
ALTER SESSION [<session_id>] ROLLBACK TRANSACTION
Syntax for setting the priority of a session:
ALTER SESSION [<session_id>] SET PRIORITY TO <priority>
Syntax for adding a tag to or removing a tag from a session:
ALTER SESSION [<session_id>] {ADD | REMOVE} TAG <tag>

Inputs

The ALTER SESSION command takes the following inputs:

Table 1. ALTER SESSION inputs
Input Description
<session_id> The ID of an active session. If a session ID is not specified, the command applies to the current session.
<priority> The priority level to which the session is to be set (CRITICAL, HIGH, NORMAL, or LOW).
<tag> The tag that is to be added to or removed from the session.

Outputs

The ALTER SESSION command produces the following outputs:

Table 2. ALTER SESSION outputs
Output Description
ALTER SESSION The command was successful.
ERROR: permission denied The user does not have permission to issue the command for the session.
ERROR: request exceeds maximum priority limit. The user attempted to raise the session priority beyond its maximum priority limit.
ERROR: id '<session_id>' does not correspond to an existing session. A session with the indicated ID does not exist.
ERROR: system session id '<session_id>' cannot be aborted The indicated session ID refers to a system session. A user cannot roll back a transaction of a system session.
ERROR: access denied. You must have ABORT privileges to perform this action The user does not have permission to roll back the transaction of the session.
ERROR: session abort failed for session <session_id>; reason is '<reason>' The attempt to roll back the active transaction of the indicated session failed for the indicated reason.

Privileges

Each session is owned by a user:
  • For a session that you own:
    • To roll back the active transaction, to set its priority up to the maximum, or to add or remove a tag, you do not need any special privileges.
    • To set the priority to a level that exceeds the maximum, you must be the admin user or have Manage System privilege.
  • For a session that is owned by another user:
    • To change the priority of the session or to add or remove a tag, you must be the admin user or have Manage System privilege.
    • To roll back the active transaction, you must have Abort privilege for the owner of the session.

Usage

The following examples illustrate how:
  • Change the priority of the current session to CRITICAL:
       system(admin)=> ALTER SESSION SET PRIORITY TO CRITICAL;
  • Roll back the active transaction of session 16011: 
       system(admin)=> ALTER SESSION 16011 ROLLBACK TRANSACTION;
  • Add the tag no_more_than_2 to session 16011: 
       system(admin)=> ALTER SESSION 16011 ADD TAG no_more_than_2;