Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

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:

Parameter Description
NLS_SORT={BINARY| SortName} Indicates which collation sequence to use for linguistic comparisons.

Append _CI or _AI to either BINARY or the SortName value if you wish 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 "Supported linguistic sorts" 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 "Length semantics and 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.
ISOLATION_LEVEL = {SERIALIZABLE|READ COMMITTED} Sets isolation level. Change takes effect starting with next transaction.

For a descriptions of the isolation levels, see Oracle TimesTen In-Memory Database Operations Guide.

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.

If PL/SQL is not enabled in your database and you specify this attribute, TimesTen throws an error.

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.

If PL/SQL is not enabled in your database and you specify this attribute, TimesTen returns an error.

For more information, see Oracle Database PL/SQL Language Reference.

PLSCOPE_SETTINGS = '{IDENTIFIERS:ALL |IDENTIFIERS:NONE}' Controls whether or not the PL/SQL compiler generates cross-reference information. Specify IDENTIFIERS:ALL to generate cross-reference information. The default is IDENTIFIERS:NONE.

If PL/SQL is not enabled in your database and you specify this attribute, TimesTen returns an error.

For more information, see Oracle Database PL/SQL Language Reference

PLSQL_CONN_MEM_LIMIT = n Specifies the maximum amount of process heap memory that PL/SQL can use for this connection. n is an integer expressed in megabytes. The default is 100.

If PL/SQL is not enabled in your database and you specify this attribute, TimesTen returns an error.

For more information, see Oracle Database PL/SQL Language Reference


Description

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.