Purpose

Use the ALTER SESSION statement to set 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.

To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.

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

Syntax

alter_session::=

Description of the illustration alter_session.eps

alter_session_set_clause::=

Description of the illustration alter_session_set_clause.eps

Semantics

ADVISE Clause

The ADVISE clause 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 values are 'C' for COMMIT, 'R' for ROLLBACK, and '  ' for NOTHING). If the transaction becomes in doubt, then 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.

CLOSE DATABASE LINK Clause

Specify CLOSE DATABASE LINK to close the database link dblink. When you issue a statement that uses a database link, Oracle Database 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, then use this clause to close the link explicitly if you do not plan to use it again in your session.

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, then specify DISABLE COMMIT IN PROCEDURE 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 COMMIT IN PROCEDURE.

Some applications automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation for more information.

ENABLE | DISABLE GUARD

The security_clause of ALTER DATABASE lets you prevent anyone other than the SYS user from making any changes to data or database objects on the primary or standby database. This clause lets you override that setting for the current session.

PARALLEL DML | DDL | QUERY

The PARALLEL parameter determines 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. Uncommitted transactions must either be committed or rolled back prior to executing this clause for DML.

ENABLE Clause

Specify ENABLE to execute subsequent statements in the session in parallel. This is the default for DDL and query statements.

• DML: DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.

• DDL: DDL statements are executed in parallel mode if a parallel clause is specified.

• QUERY: Queries are executed in parallel mode if a parallel hint or a parallel clause is specified.

Restriction on the ENABLE clause

You cannot specify the optional PARALLEL integer with ENABLE.

DISABLE Clause

Specify DISABLE to execute subsequent statements in the session serially. This is the default for DML statements.

• DML: DML statements are executed serially.

• DDL: DDL statements are executed serially.

• QUERY: Queries are executed serially.

Restriction on the DISABLE clause

You cannot specify the optional PARALLEL integer with DISABLE.

FORCE Clause

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, subsequent DML statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause.

• DDL: Subsequent DDL statements in the session are executed with the default degree of parallelism, unless a degree is specified in this clause. Resulting database objects will have associated with them the prevailing degree of parallelism.

  Specifying 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 the default degree) in the CREATE TABLE statement.

• QUERY: Subsequent queries are executed with the default degree of parallelism, unless a degree is specified in this clause.

PARALLEL integer

Specify an integer to explicitly specify a degree of parallelism:

• For FORCE DDL, the degree overrides any parallel clause in subsequent DDL statements.

• 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 cluster 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 data types

RESUMABLE Clauses

These clauses let you enable and disable resumable space allocation. This feature allows an operation to be suspended in the event of an out-of-space error condition and to resume automatically from the point of interruption when the error condition is fixed.

ENABLE RESUMABLE

This clause enables resumable space allocation for the session.

TIMEOUT

TIMEOUT lets you specify (in seconds) the time during which an operation can remain suspended while waiting for the error condition to be fixed. If the error condition is not fixed within the TIMEOUT period, then Oracle Database aborts the suspended operation.

NAME

NAME lets you specify a user-defined text string to help users identify the statements issued during the session while the session is in resumable mode. Oracle Database inserts the text string into the USER_RESUMABLE and DBA_RESUMABLE data dictionary views. If you do not specify NAME, then Oracle Database inserts the default string 'User username(userid), Session sessionid, Instance instanceid'.

DISABLE RESUMABLE

This clause disables resumable space allocation for the session.

SHARD DDL Clauses

These clauses are valid only if you are connected to a sharded database. They let you control whether DDLs issued in the session are issued against the shard catalog database and all shards, or against only the shard catalog database.

• If you specify ENABLE SHARD DDL, then DDLs issued in the session are issued against the shard catalog database and all shards. This mode is the default for the SDB user—a user that exists in the shard catalog database and in all shards.

• If you specify DISABLE SHARD DDL, then DDLs issued in the session are issued against only the shard catalog database. This mode is the default for a local user—a user that exists only in the shard catalog database.

SYNC WITH PRIMARY

Use this clause to synchronize redo apply on a physical standby database with the primary database. An ALTER SESSION statement with this clause blocks until redo apply has applied all redo data received by the standby at the time the statement is issued. This clause returns an error, and synchronization does not occur, if the redo transport state for the standby database is not SYNCHRONIZED or if redo apply is not active.

alter_session_set_clause

Use the alter_session_set_clause to set initialization parameter values or to set an edition for the current session.

Initialization Parameters

You can set two types of parameters using this clause:

• Initialization parameters that are dynamic in the scope of the ALTER SESSION statement (listed in "Initialization Parameters and ALTER SESSION")

• Session parameters (listed in "Session Parameters and ALTER SESSION")

You can set values for multiple parameters in the same alter_session_set_clause.

EDITION

Specify EDITION = edition to set the specified edition as the edition in the database session. You must have the USE object privilege on edition, edition must already have been created, and it must be USABLE.

When this statement is successful, the database discards PL/SQL package state corresponding to editionable packages but retains package state corresponding to packages that are not editionable.

You can also set the edition for the current session at startup with the EDITION parameter of the SQL*Plus CONNECT command. However, you cannot specify an ALTER SESSION SET EDITION statement in a recursive SQL or PL/SQL block.

You can determine the edition in use by the current session with the following query:

SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM DUAL;

CONTAINER

Use this clause in a multitenant container database (CDB) to switch to the container specified by container_name.

To use this clause, you must be a common user with the SET CONTAINER privilege, either granted commonly or granted locally in container_name.

For container_name, specify one of the following:

• CDB$ROOT to switch to the root

• PDB$SEED to switch to the seed

• A pluggable database (PDB) name to switch to that PDB. You can view the names of the PDBs in a CDB by querying the DBA_PDBS view.

You can determine the container to which the current session is connected by using the SQL*Plus SHOW CON_NAME command or with the following SQL query:

SELECT SYS_CONTEXT('USERENV', 'CON_NAME') FROM DUAL;

SERVICE

By default, when you switch to a container, the session uses the default service for the container. Specify the SERVICE clause to use a different service for the container. For service_name, specify the name of the service you want to use.

ROW ARCHIVAL VISIBILITY

Use this clause to configure row archival visibility for the session. This clause lets you implement In-Database Archiving, which allows you to designate table rows as active or archived. You can then perform queries on only the active rows within the table.

• If you specify ACTIVE, then the database will consider only active rows when performing queries on tables that are enabled for row archival. This is the default.

• If you specify ALL, then the database will consider all rows when performing queries on tables that are enabled for row archival.

This clause has no effect on queries on tables that are not enabled for row archival.

DEFAULT_COLLATION

Use this clause to set the default collation for the session.

• Use collation_name to specify the default collation for the session. You can specify the name of any valid named collation or pseudo-collation. This collation becomes the effective schema default collation. This collation is assigned to tables, views, and materialized views that are subsequently created in any schema for the duration of the session. The default collation for the session does not get propagated to any remote sessions connected to the current session using DB links.

• If you specify NONE, then there is no default collation for the session. In this case, the default collation for a particular schema becomes the effective schema default collation for that schema. That default collation is assigned to tables, views, and materialized views that are subsequently created in the schema for the duration of the session.

In either of the preceding cases, you can override the effective schema default collation and assign a default collation to a particular table, materialized view, or view by specifying the DEFAULT COLLATION clause of the CREATE or ALTER statement for the table, materialized view, or view.

The effective schema default collation also affects the DDL statements CREATE FUNCTION, CREATE PACKAGE, CREATE PROCEDURE, CREATE TRIGGER, and CREATE TYPE. Refer to Oracle Database PL/SQL Language Reference for more details on these statements.

You can query the default collation for a session with the following statement:

SELECT SYS_CONTEXT('USERENV', 'SESSION_DEFAULT_COLLATION') FROM DUAL;

You can specify the SET DEFAULT_COLLATION clause only if the COMPATIBLE initialization parameter is set to 12.2 or greater, and the MAX_STRING_SIZE initialization parameter is set to EXTENDED.