ALTER SESSION

The ALTER SESSION statement changes session parameters dynamically. This overrides the setting of the equivalent connection attribute for the current session, as applicable.

Required Privilege

None

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout. However, these parameters are not supported:

DDL_REPLICATION_ACTION
DDL_REPLICATION_LEVEL
REPLICATION_TRACK

SQL Syntax

ALTER SESSION SET
  {COMMIT_BUFFER_SIZE_MAX = n |
   DDL_REPLICATION_ACTION={'INCLUDE'|'EXCLUDE'} | 
   DDL_REPLICATION_LEVEL={1|2|3} |
   ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED} |
   NLS_SORT = {BINARY| SortName} |
   NLS_LENGTH_SEMANTICS = {BYTE|CHAR} |
   NLS_NCHAR_CONV_EXCP = {TRUE|FALSE} |
   PLSQL_TIMEOUT = n |
   PLSQL_OPTIMIZE_LEVEL = {0|1|2|3}|
   PLSQL_CONN_MEM_LIMIT = n |
   PLSQL_CCFLAGS = 'name1:value1, name2:value2,..., nameN:valueN' |
   PLSQL_SESSION_CACHED_CURSORS = n |
   REPLICATION_TRACK = TrackNumber |
   }

Parameters

Parameter	Description
`COMMIT_BUFFER_SIZE_MAX=` `n`	Changes the maximum size of the commit buffer when a connection is in progress. n is expressed as an integer and represents the maximum size of the commit buffer (in MB). Change takes effect starting with the next transaction. Call the `ttConfiguration` built-in procedure to see the currently configured maximum size of the commit buffer. A value of 0 means the buffer is configured with a default size. The default size is 128 KB. For more information on the commit buffer and transaction reclaim operations, see Transaction Reclaim Operations in the Oracle TimesTen In-Memory Database Operations Guide and CommitBufferSizeMax in the Oracle TimesTen In-Memory Database Reference. Note: The equivalent connection attribute is `CommitBufferSizeMax`.
`DDL_REPLICATION_ACTION={'INCLUDE'\|'EXCLUDE'}`	To include a table or sequence in the active standby pair when either is created, set `DDL_REPLICATION_ACTION` to `INCLUDE`. If you do not want to include a table or sequence in the active standby pair when either is created, set `DDL_REPLICATION_ACTION` to `EXCLUDE`. The default is `INCLUDE`. If set to `EXCLUDE`: A subsequent `ALTER ACTIVE STANDBY PAIR` ... `INCLUDE TABLE` is required to be executed on the active database to add the table to the replication scheme. All tables must be empty on all active standby databases and subscribers as the table contents will be truncated when this statement is executed. A subsequent `ALTER ACTIVE STANDBY PAIR` ... `INCLUDE SEQUENCE` is required to be executed on the active database to add the sequence to the replication scheme. This attribute is valid only if `DDL_REPLICATION_LEVEL` is 2 or greater. See Making DDL Changes in an Active Standby Pair in the Oracle TimesTen In-Memory Database Replication Guide for more information. Note: The equivalent connection attribute is `DDLReplicationAction`.
`DDL_REPLICATION_LEVEL={1\|2\|3}`	Indicates whether DDL is replicated across all databases in an active standby pair. The value can be one of the following: 1: Default. Add or drop a column to or from a replicated table on the active database using `ALTER TABLE`. The change is replicated to the table in the standby database. 2: Supports replication of the creation or dropping of tables, synonyms or indexes from the active database to the standby database. This does include creating or dropping global temporary tables, but does not include `CREATE TABLE AS SELECT`. The statement is replicated only when the index is created on an empty table. 3: Supports replication of all DDL supported by level 2 as well as replication of creation or dropping of views and sequences (not including materialized views) and changes to the cache administration user ID and password settings when you call the `ttCacheUidPwdSet` built-in procedure. Note: After you have defined cache groups, you cannot change the cache administration user ID, but can still change the cache administration password. See Making DDL Changes in an Active Standby Pair in the Oracle TimesTen In-Memory Database Replication Guide for more information. Note: The equivalent connection attribute is `DDLReplicationLevel`.
`ISOLATION_LEVEL = {SERIALIZABLE\|READ COMMITTED}`	Sets isolation level. Change takes effect starting with the next transaction. For a descriptions of the isolation levels, see Transaction Isolation Levels in the Oracle TimesTen In-Memory Database Operations Guide. Note: The equivalent connection attribute is `Isolation`.
`NLS_SORT={BINARY\|` `SortName}`	Indicates which collation sequence to use for linguistic comparisons. Append `_CI` or `_AI` to either `BINARY` or the `SortName` value to do case-insensitive or accent-insensitive sorting. If you do not specify `NLS_SORT`, the default is `BINARY`. For a complete list of supported values for `SortName`, see Linguistic Sort Rules Support Linguistic Conventions in Oracle TimesTen In-Memory Database Operations Guide. For more information on case-insensitive or accent-insensitive sorting, see Case-Insensitive and Accent-Insensitive Linguistic Sorts in Oracle TimesTen In-Memory Database Operations Guide.
`NLS_LENGTH_SEMANTICS ={BYTE\|CHAR}`	Sets the default length semantics configuration. `BYTE` indicates byte length semantics. `CHAR` indicates character length semantics. The default is `BYTE`. For more information on length semantics, see Character Set Length Semantics Affect Data Storage in Oracle TimesTen In-Memory Database Operations Guide.
`NLS_NCHAR_CONV_EXCP =` `{TRUE\|FALSE}`	Determines whether an error should be reported when there is data loss during an implicit or explicit character type conversion between `NCHAR`/`NVARCHAR2` data and `CHAR`/`VARCHAR2` data. Specify `TRUE` to enable error reporting. Specify `FALSE` to not report errors. The default is `FALSE`.
`PLSQL_TIMEOUT=` `n`	Controls how long PL/SQL procedures run before being automatically terminated. `n` represents the time, in seconds. Specify 0 for no time limit or any positive integer. The default is 30. When you modify this value, the new value impacts PL/SQL program units that are currently running as well as any other program units subsequently executed in the same connection. See Choose SQL and PL/SQL Timeout Values in the Oracle TimesTen In-Memory Database Operations Guide for information on setting timeout values.
`PLSQL_OPTIMIZE_LEVEL = {0\|1\|2\|3}`	Specifies the optimization level used to compile PL/SQL library units. The higher the setting, the more effort the compiler makes to optimize PL/SQL library units. Possible values are 0, 1, 2 or 3. The default is 2. For more information, see PLSQL_OPTIMIZE_LEVEL in Oracle TimesTen In-Memory Database Reference.
`PLSQL_CONN_MEM_LIMIT =` `n`	Specifies the maximum amount of process heap memory that PL/SQL can use for this connection, where `n` is an integer expressed in MB. The default is 100. For more information, see PLSQL_CONN_MEM_LIMIT in Oracle TimesTen In-Memory Database Reference.
`PLSQL_CCFLAGS = 'name1:value1, name2:value2, ..., nameN:valueN'`	Specifies inquiry directives to control conditional compilation of PL/SQL units, which enables you to customize the functionality of a PL/SQL program depending on conditions that are checked. For example, to activate debugging features: PLSQL_CCFLAGS = 'DEBUG:TRUE'
`PLSQL_SESSION_CACHED_CURSORS=` `n`	Specifies the maximum number of session cursors to cache. The default is 50. The range of values is 1 to 65535. The `PLSQL_SESSION_CACHED_CURSORS` setting in TimesTen behaves the same as the `SESSION_CACHED_CURSORS` setting in Oracle RDBMS.
`REPLICATION_TRACK =` `TrackNumber`	When managing track-based parallel replication, you can assign a connection to a replication track. All transactions issued by the connection are assigned to this track, unless the track is altered. If the number specified is for a non-existent replication track `X`, the transaction is assigned to a track number computed as `X` modulo `ReplicationParallelism`. You cannot change tracks in the middle of a transaction unless all preceding operations have been read operations. For more information, see Specifying Replication Tracks Within an Automatic Parallel Replication Environment in Oracle TimesTen In-Memory Database Replication Guide. The equivalent connection attribute is `ReplicationTrack`.

Description

The ALTER SESSION statement affects commands that are subsequently executed by the session. ALTER SESSION does not do an implicit commit.
In cases of client failover, if an ALTER SESSION statement is issued in the failed connection, the setting is not seen or carried over to the new connection. You must re-issue the ALTER SESSION statement and re-specify the value for that parameter. For more information on client failover, in TimesTen Classic, see Using Automatic Client Failover in the Oracle TimesTen In-Memory Database Operations Guide and, in TimesTen Scaleout, see Client Connection Failover in the Oracle TimesTen In-Memory Database Scaleout User's Guide.
Operations involving character comparisons support linguistic sensitive collating sequences. Case-insensitive sorts may affect DISTINCT value interpretation.
Implicit and explicit conversions between CHAR and NCHAR are supported.
You can use the SQL string functions with the supported character sets. For example, UPPER and LOWER functions support non-ASCII CHAR and VARCHAR2 characters as well as NCHAR and NVARCHAR2 characters.
Choice of character set could have an impact on memory consumption for CHAR and VARCHAR2 column data.
The character sets of all databases involved in a replication scheme must match.
To add an existing table to an active standby pair, set DDL_REPLICATION_LEVEL to 2 or greater and DDL_REPLICATION_ACTION to INCLUDE. Alternatively, you can use the ALTER ACTIVE STANDBY PAIR ... INCLUDE TABLE statement if DDL_REPLICATION_ACTION is set to EXCLUDE. In this case, the table must be empty and present on all databases before executing the ALTER ACTIVE STANDBY PAIR ... INCLUDE TABLE statement as the table contents will be truncated when this statement is executed.
To add an existing sequence or view to an active standby pair, set DDL_REPLICATION_LEVEL to 3. To include the sequence in the replication scheme, DDL_REPLICATION_ACTION must be set to INCLUDE. This does not apply to materialized views.
Objects are replicated only when the receiving database is of a TimesTen release that supports that level of replication, and is configured for an active standby pair replication scheme. For example, replication of sequences (requiring DDL_REPLICATION_LEVEL=3) to a database release prior to 11.2.2.7.0 is not supported. The receiving database must be of at least release 11.2.1.8.0 for replication of objects supported by DDL_REPLICATION_LEVEL=2.

Examples

Use the ALTER SESSION statement to change COMMIT_BUFFER_SIZE_MAX to 500 MB. First, call ttConfiguration to display the current connection setting. Use the ALTER SESSION statement to change the COMMIT_BUFFER_SIZE_MAX setting to 500. Call ttConfiguration to display the new setting.

Command>  CALL ttConfiguration ('CommitBufferSizeMax');
< CommitBufferSizeMax, 0 >
1 row found.
Command> ALTER SESSION SET COMMIT_BUFFER_SIZE_MAX = 500;
 
Session altered.
 
Command> CALL ttConfiguration ('CommitBufferSizeMax');
< CommitBufferSizeMax, 500 >
1 row found.

Use the ALTER SESSION statement to change PLSQL_TIMEOUT to 60 seconds. Use a second ALTER SESSION statement to change PLSQL_OPTIMIZE_LEVEL to 3. Then call ttConfiguration to display the new values.

Command> ALTER SESSION SET PLSQL_TIMEOUT = 60;
Session altered.
Command> ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
Session altered.

Command> CALL TTCONFIGURATION ();
< CkptFrequency, 600 >
< CkptLogVolume, 0 >
< CkptRate, 0 >
...
< PLSQL_OPTIMIZE_LEVEL, 3 >
< PLSQL_TIMEOUT, 60 >
...
47 rows found.

In this example, set PLSQL_TIMEOUT to 20 seconds. Attempt to execute a program that loops indefinitely. In 20 seconds, execution is terminated and an error is returned.

Command> ALTER SESSION SET PLSQL_TIMEOUT = 20;

Command> DECLARE v_timeout NUMBER;
         BEGIN
           LOOP
             v_timeout :=0;
             EXIT WHEN v_timeout < 0;
           END LOOP;
         END;
         /
 8509: PL/SQL execution terminated; PLSQL_TIMEOUT exceeded

The following example uses the ALTER SESSION statement to change the NLS_SORT setting from BINARY to BINARY_CI to BINARY_AI. The database and connection character sets are WE8ISO8859P1.

Command> connect "dsn=cs;ConnectionCharacterSet=WE8ISO8859P1";
Connection successful: DSN=cs;UID=user;DataStore=/datastore/user/cs;
DatabaseCharacterSet=WE8ISO8859P1;
ConnectionCharacterSet=WE8ISO8859P1;PermSize=32;
(Default setting AutoCommit=1)
Command> -- Create the Table
Command> CREATE TABLE collatingdemo (letter VARCHAR2 (10));
Command> -- Insert values
Command> INSERT INTO collatingdemo VALUES ('a');
1 row inserted.
Command> INSERT INTO collatingdemo VALUES ('A');
1 row inserted.
Command> INSERT INTO collatingdemo VALUES ('Y');
1 row inserted.
Command> INSERT INTO collatingdemo VALUES ('ä');
1 row inserted.
Command> -- SELECT
Command> SELECT * FROM collatingdemo;
< a >
< A >
< Y >
< ä >
4 rows found.
Command> --SELECT with ORDER BY
Command> SELECT * FROM collatingdemo ORDER BY letter;
< A >
< Y >
< a >
< ä >
4 rows found.
Command>-- set NLS_SORT to BINARY_CI and SELECT
Command> ALTER SESSION SET NLS_SORT = BINARY_CI;
Command> SELECT * FROM collatingdemo ORDER BY letter;
< a >
< A >
< Y >
< Ä >
< ä >
4 rows found.
Command> -- Set NLS_SORT to BINARY_AI and SELECT
Command> ALTER SESSION SET NLS_SORT = BINARY_AI;
Command> SELECT * FROM collatingdemo ORDER BY letter;
< ä >
< a >
< A >
< Y >
4 rows found.

The following example enables automatic parallel replication with disabled commit dependencies. It uses the ALTER SESSION statement to change the replication track number to 5 for the current connection. To enable automatic parallel replication with disabled commit dependencies for replication schemes, set ReplicationApplyOrdering to 2. Then, always set REPLICATION_TRACK to a number less than or equal to ReplicationParallelism. For example, the ReplicationParallelism connection attribute could be set to 6, which is higher than the value of 5 set for REPLICATION_TRACK.

Command> ALTER SESSION SET REPLICATION_TRACK = 5;
Session altered.

The following example enables replication of adding and dropping columns, tables, synonyms and indexes by setting the following on the active database in an alter standby replication pair: DDL_REPLICATON_LEVEL set to 2 and DDLReplicationAction set to 'INCLUDE'.

Command > ALTER SESSION SET DDL_REPLICATION_LEVEL=2;
Session altered.

Command > ALTER SESSION SET DDL_REPLICATION_ACTION='INCLUDE';
Session altered.

Note:

The equivalent connection attributes for DDL_REPLICATION_LEVEL and DDL_REPLICATION_ACTION are DDLReplicationLevel and DDLReplicationAction, respectively.