SQL Statements (continued), 2 of 5

ALTER SESSION

Syntax

Purpose

To specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database.

Prerequisites

To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.

You do not need any privileges to perform the other operations of this statement unless otherwise indicated.

Keywords and Parameters

Examples

PARALLEL Example

Issue the following statement to enable parallel DML mode for the current session:

ALTER SESSION ENABLE PARALLEL DML;

ADVISE Example

The following transaction inserts an employee record into the EMP table on the database identified by the database link SITE1 and deletes an employee record from the EMP table on the database identified by SITE2:

ALTER SESSION
   ADVISE COMMIT; 

INSERT INTO emp@site1
   VALUES (8002, 'FERNANDEZ', 'ANALYST', 7566,
   TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 3000, NULL, 20); 

ALTER SESSION
   ADVISE ROLLBACK; 

DELETE FROM emp@site2
   WHERE empno = 8002; 

COMMIT;

This transaction has two ALTER SESSION statements with the ADVISE clause. If the transaction becomes in doubt, SITE1 is sent the advice 'COMMIT' by virtue of the first ALTER SESSION statement and SITE2 is sent the advice 'ROLLBACK' by virtue of the second.

CLOSE DATABASE LINK Example

This statement updates the employee table on the SALES database using a database link, commits the transaction, and explicitly closes the database link:

UPDATE emp@sales 
   SET sal = sal + 200
   WHERE empno = 9001;

COMMIT; 

ALTER SESSION
   CLOSE DATABASE LINK sales;

Date Format Example

The following statement dynamically changes the default date format for your session to 'YYYY MM DD-HH24:MI:SS':

ALTER SESSION 
   SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

Oracle uses the new default date format:

SELECT TO_CHAR(SYSDATE) Today
   FROM DUAL; 

TODAY 
------------------- 
1997 08 12 14:25:56

Date Language Example

The following statement changes the language for date format elements to French:

ALTER SESSION 
   SET NLS_DATE_LANGUAGE = French;

SELECT TO_CHAR(SYSDATE, 'Day DD Month YYYY') Today
   FROM DUAL; 

TODAY 
--------------------------- 
Mardi    28 Février   1997

ISO Currency Example

The following statement dynamically changes the ISO currency symbol to the ISO currency symbol for the territory America:

ALTER SESSION
   SET NLS_ISO_CURRENCY = America; 

SELECT TO_CHAR( SUM(sal), 'C999G999D99') Total
   FROM emp; 

TOTAL 
-------------
USD29,025.00

Decimal Character and Group Separator Example

The following statement dynamically changes the decimal character to comma (,) and the group separator to period (.):

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.' ;

Oracle returns these new characters when you use their number format elements:

SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp ;

TOTAL 
-------------
FF29.025,00

NLS Currency Example

The following statement dynamically changes the local currency symbol to 'DM':

ALTER SESSION
   SET NLS_CURRENCY = 'DM'; 

SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total
   FROM emp; 

TOTAL 
-------------
DM29.025,00

NLS Language Example

The following statement dynamically changes to French the language in which error messages are displayed:

ALTER SESSION
   SET NLS_LANGUAGE = FRENCH; 

SELECT * FROM DMP;

ORA-00942: Table ou vue inexistante

Linguistic Sort Example

The following statement dynamically changes the linguistic sort sequence to Spanish:

ALTER SESSION
   SET NLS_SORT = XSpanish;

Oracle sorts character values based on their position in the Spanish linguistic sort sequence.

SQL Trace Example

To enable the SQL trace facility for your session, issue the following statement:

ALTER SESSION 
   SET SQL_TRACE = TRUE;

Query Rewrite Example

This statement enables query rewrite in the current session for all materialized views that have not been explicitly disabled:

ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

`ADVISE`	sends advice to a remote database to force a distributed transaction. The advice appears in the `ADVICE` column of the `DBA_2PC_PENDING` view on the remote database (the value 'C' for `COMMIT`, 'R' for `ROLLBACK`, and ' ' for `NOTHING`). If the transaction becomes in doubt, the administrator of that database can use this advice to decide whether to commit or roll back the transaction. You can send different advice to different remote databases by issuing multiple `ALTER` `SESSION` statements with the `ADVISE` clause in a single transaction. Each such statement sends advice to the databases referenced in the following statements in the transaction until another such statement is issued. For more information on distributed transactions and how to decide whether to commit or roll back in-doubt distributed transactions, see Oracle8i Distributed Database Systems.
`CLOSE DATABASE LINK`	closes the database link dblink. When you issue a statement that uses a database link, Oracle creates a session for you on the remote database using that link. The connection remains open until you end your local session or until the number of database links for your session exceeds the value of the initialization parameter `OPEN_LINKS`. If you want to reduce the network overhead associated with keeping the link open, use this clause to close the link explicitly if you do not plan to use it again in your session. You must first close all cursors that use the link and then end your current transaction if it uses the link.
`ENABLE \| DISABLE COMMIT IN PROCEDURE`	Procedures and stored functions written in PL/SQL can issue `COMMIT` and `ROLLBACK` statements. If your application would be disrupted by a `COMMIT` or `ROLLBACK` statement not issued directly by the application itself, use the `DISABLE` form of this clause to prevent procedures and stored functions called during your session from issuing these statements. You can subsequently allow procedures and stored functions to issue `COMMIT` and `ROLLBACK` statements in your session by issuing the `ENABLE` form of this clause. Some applications (such as SQL*Forms) automatically prohibit `COMMIT` and `ROLLBACK` statements in procedures and stored functions. Refer to your application documentation.
	Note: This statement does not apply to database triggers. Triggers can never issue `COMMIT` or `ROLLBACK` statements.
`PARALLEL DML \| DDL \| QUERY`	specifies whether all subsequent DML, DDL, or query statements in the session will be considered for parallel execution. This clause enables you to override the degree of parallelism of tables during the current session without changing the tables themselves. You can execute this clause for DML only between committed transactions. Uncommitted transactions must either be committed or rolled back prior to executing this clause for DML.
	`ENABLE`	executes subsequent statements in the session in parallel. This is the default for DDL and query statements.
		DML: executes the session's DML statements in parallel mode if a parallel hint or a parallel clause is specified. DDL: executes the session's DDL statements in parallel mode if a parallel clause is specified. QUERY: executes the session's queries in parallel mode if a parallel hint or a parallel clause is specified Restriction: You cannot specify the optional `PARALLEL` integer with `ENABLE`.
	`DISABLE`	specifies that subsequent statements will be executed serially. This is the default for DML statements.
		DML: executes the session's DML statements serially. DDL: executes the session's DDL statements serially. QUERY: executes the session's queries serially. Restriction: You cannot specify the optional `PARALLEL` integer with `DISABLE`.
	`FORCE`	forces parallel execution of subsequent statements in the session. If no parallel clause or hint is specified, then a default degree of parallelism is used. This clause overrides any parallel_clause specified in subsequent statements in the session, but is overridden by a parallel hint.
		DML: provided no parallel DML restrictions are violated, executes subsequent DML statements in the session with the default degree of parallelism, unless a specific degree is specified in this clause. DDL: executes subsequent DDL statements in the session with the default degree of parallelism, unless a specific degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism. Using `FORCE DDL` automatically causes all tables created in this session to be created with a default level of parallelism. The effect is the same as if you had specified the parallel_clause (with default degree) with the `CREATE` `TABLE` statement. QUERY: executes subsequent queries with the default degree of parallelism, unless a specific degree is specified in this clause.
		`PARALLEL` integer: explicitly specifies a degree of parallelism - For force DDL, the degree overrides any parallel clause in subsequent DDL statments. - For force DML and QUERY, the degree overrides the degree currently stored for the table in the data dictionary. - A degree specified in a statement through a hint will override the degree being forced.
	The following types of DML operations are not parallelized regardless of this clause: Operations on clustered tables Operations with embedded functions that either write or read database or package states Operations on tables with triggers that could fire Operations on tables or schema objects containing object types, or LONG or LOB datatypes.
	For a detailed description of parallel DML features and hints, see Oracle8i Designing and Tuning for Performance.
*set_clause*	sets the session parameters that follow. You can set values for multiple parameters in the same set_clause.
	CAUTION: Unless otherwise indicated, the parameters described here are initialization parameters, and the descriptions indicate only the general nature of the parameters. Before changing the values of initialization parameters, please refer to their full description in *Oracle8i Reference* or *Oracle8i National Language Support Guide*.
`CONSTRAINT{S} = {IMMEDIATE \| DEFERRED \| DEFAULT }`
	determines when conditions specified by a deferrable constraint are enforced. `CONSTRAINT{S}` is a session parameter only, not an initialization parameter.
	`IMMEDIATE` indicates that the conditions specified by the deferrable constraint are checked immediately after each DML statement. This setting is equivalent to issuing the `SET CONSTRAINTS ALL IMMEDIATE` statement at the beginning of each transaction in your session. See the `IMMEDIATE` parameter of "SET CONSTRAINT(S)".
	`DEFERRED` indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed. This setting is equivalent to issuing the `SET` `CONSTRAINTS ALL DEFERRED` statement at the beginning of each transaction in your session. See the `DEFERRED` parameter of "SET CONSTRAINT(S)". `DEFAULT` restores all constraints at the beginning of each transaction to their initial state of `DEFERRED` or `IMMEDIATE`.
`CREATE_STORED_OUTLINES = { TRUE \| FALSE \| 'category_name' }`
	determines whether Oracle should automatically create and store an outline for each query submitted during the session. `CREATE_STORED_OUTLINES` is not an initialization parameter.
	`TRUE` enables automatic outline creation for subsequent queries in the same session. These outlines receive a unique system-generated name and are stored in the `DEFAULT` category. If a particular query already has an outline defined for it in the `DEFAULT` category, that outline will remain and a new outline will not be created. `FALSE` disables automatic outline creation during the session. This is the default. category_name has the same behavior as `TRUE` except that any outline created during the session is stored in the category_name category.
`CURRENT_SCHEMA = schema`
	changes the current schema of the session to the specified schema. Subsequent unqualified references to schema objects during the session will resolve to objects in the specified schema. The setting persists for the duration of the session or until you issue another `ALTER SESSION SET CURRENT_SCHEMA` statement. `CURRENT_SCHEMA` is a session parameter only, not an initialization parameter.
	This setting offers a convenient way to perform operations on objects in a schema other than that of the current user without having to qualify the objects with the schema name. This setting changes the current schema, but it does not change the session user or the current user, nor does it give you any additional system or object privileges for the session. For more information on this parameter, see Oracle8i Application Developer's Guide - Fundamentals.
`CURSOR_SHARING = {FORCE \| EXACT}`
	determines what kind of SQL statements can share the same cursors. `EXACT` causes only identical SQL statements to share a cursor. `FORCE` forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement. See Also: Oracle8i Designing and Tuning for Performance for information on setting this parameter in these and other environments.
`DB_BLOCK_CHECKING = {TRUE \| FALSE}`
	controls whether data block checking is done. The default is `FALSE`.
`DB_FILE_MULTIBLOCK_READ_COUNT = integer`
	specifies with integer the maximum number of blocks read in one I/O operation during a sequential scan. The default is 8.
`FAST_START_IO_TARGET = integer`
	specifies the target number of I/Os (reads and writes) to and from buffer cache that Oracle should perform upon crash or instance recovery. Oracle continuously calculates the actual number of I/Os that would be needed for recovery and compares that number against the target. If the actual number is greater than the target, Oracle attempts to write additional dirty buffers to advance the checkpoint, while minimizing the affect on performance. For information on how to tune this parameter, see Oracle8i Designing and Tuning for Performance.
`FLAGGER = { ENTRY \| INTERMEDIATE \| FULL \| OFF }`
	specifies FIPS flagging, which causes an error message to be generated when a SQL statement issued is an extension of ANSI SQL92. `FLAGGER` is a session parameter only, not an initialization parameter. In Oracle, there is currently no difference between Entry, Intermediate, or Full level flagging. Once flagging is set in a session, a subsequent `ALTER SESSION SET FLAGGER` statement will work, but generates the message, ORA-00097. This allows FIPS flagging to be altered without disconnecting the session. `OFF` turns off flagging.
`GLOBAL_NAMES = { TRUE \| FALSE }`
	When you start an instance, Oracle determines whether to enforce global name resolution for remote objects accessed in SQL statements based on the value of the initialization parameter `GLOBAL_NAMES`. This parameter enables or disables global name resolution for the duration of the session. `TRUE` enables the enforcement of global names. `FALSE` disables the enforcement of global names. You can also enable or disable global name resolution for your instance with the `GLOBAL_NAMES` parameter of the `ALTER SYSTEM` statement. Oracle recommends that you enable global name resolution if you use or plan to use distributed processing. For more information on global name resolution and how Oracle enforces it, see "Referring to Objects in Remote Databases" and Oracle8i Distributed Database Systems.
`HASH_AREA_SIZE = integer`
	specifies in bytes the amount of memory to use for hash join operations. The default is twice the value of the `SORT_AREA_SIZE` initialization parameter.