ALTER SESSION

The ALTER SESSION statement changes session parameters dynamically.

Required privilege

None

SQL syntax

ALTER SESSION SET
  {NLS_SORT = {BINARY| SortName} |
   NLS_LENGTH_SEMANTICS = {BYTE|CHAR} |
   NLS_NCHAR_CONV_EXCP = {TRUE|FALSE} |
   ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED} |
   PLSQL_TIMEOUT = n |
   PLSQL_OPTIMIZE_LEVEL = {0|1|2|3}|
   PLSCOPE_SETTINGS = {'IDENTIFIERS:ALL'|'IDENTIFIERS:NONE'} |
   PLSQL_CONN_MEM_LIMIT = n
   } ...

Parameters

The ALTER SESSION statement has the parameters:

Description

• The ALTER SESSION statement affects commands that are subsequently executed by the session. The new session parameters take effect immediately.

• The NLS_SORT setting affects materialized views and cache group maintenance. Use the NLSSORT() SQL function rather than relying on the NLS_SORT setting.

• Character length and byte length semantics are supported to resolve potential ambiguity regarding column length and storage size. Multibyte encoding character sets are supported (For example, UTF-8 or AL32UTF8). Multibyte encodings require varying amounts of storage per character depending on the character. For example, an UTF-8 character may require from 1 to 4 bytes.

  If, for example, a column is defined as CHAR (10), you may assume that the 10 characters fit in this column regardless of character set encoding. However, for UTF-8 character set encoding, up to 40 bytes are required. TimesTen supports character length and byte length semantics to avoid such ambiguity.

• Operations involving character comparisons support linguistic sensitive collating sequences. Case-insensitive sorts may affect DISTINCT value interpretation. Supported collating sequence sensitive operations:

  • MIN,MAX

  • BETWEEN

  • =,!=, >, >=,<,<=

  • DISTINCT

  • CASE

  • GROUP BY

  • HAVING

  • ORDER BY

  • IN

  • LIKE

• Primary key indexes are based on the BINARY collating sequence. You cannot use nonbinary NLS_SORT with equality searches on the primary key index.

• Implicit and explicit conversions between CHAR and NCHAR are supported.

• Conversions between CHAR and NCHAR are not allowed when using the TIMESTEN8 character set.

• 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.

• TIMESTEN8 character set restrictions:

  • Character set conversions are not allowed.

  • BINARY is the only acceptable collating sequence.

  • CHAR semantics are ignored. Characters are assumed to be single-byte.

  • UPPER and LOWER functions support ASCII characters only. Results for non-ASCII characters are undefined. TimesTen does not return an error.

• NLS_SORT settings other than BINARY could have a performance impact on character operations.

• Choice of character set could have an impact on memory consumption for CHAR and VARCHAR2 column data.

• The character sets of all data stores involved in a replication scheme must match.

Examples

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

Call ttConfiguration to display the current PLSCOPE_SETTINGS value. Use the ALTER SESSION statement to change the PLSCOPE_SETTINGS value to IDENTIFIERS:ALL. Create a dummy procedure p. Query the system view SYS.USER_PLSQL_OBJECT_SETTINGS to confirm that the new setting is applied to procedure p.

Command> CALL TTCONFIGURATION ();< CkptFrequency, 600 >< CkptLogVolume, 0 >< CkptRate, 0 >
...< PLSCOPE_SETTINGS, IDENTIFIERS:NONE >...47 rows found.

Command> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
 
Session altered.
 
Command> CREATE OR REPLACE PROCEDURE p IS
       > BEGIN
       >  NULL;
       > END;
       > /
 
Procedure created.
 
Command> SELECT PLSCOPE_SETTINGS FROM SYS.USER_PLSQL_OBJECT_SETTINGS WHERE
       > NAME = 'p';
< IDENTIFIERS:ALL >
1 row found.

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;TypeMode=0;
(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.