Alter session oracle что это
43
DBMS_SESSION
This package provides access to SQL ALTER SESSION and SET ROLE statements, and other session information, from PL/SQL. You can use this to set preferences and security levels.
Requirements
This package runs with the privileges of calling user, rather than the package owner SYS .
Summary of Subprograms
Table 43-1 DBMS_SESSION Subprograms
Turns tracing on or off.
Sets national language support (NLS).
Closes database link.
Deinstantiates all packages in the session.
Returns an identifier that is unique for all sessions currently connected to this database.
Determines if the named role is enabled for the session.
Determines if the specified session is alive.
Turns close_cached_open_cursors on or off.
Lets you reclaim unused memory after performing operations requiring large amounts of memory.
Sets or resets the value of a context attribute.
Returns a list of active namespace and context for the current session.
SET_ROLE procedure
This procedure enables and disables roles. It is equivalent to the SET ROLE SQL statement.
Syntax
Parameters
Table 43-2 SET_ROLE Procedure Parameters
SET_SQL_TRACE procedure
This procedure turns tracing on or off. It is equivalent to the following SQL statement:
Syntax
Parameters
Table 43-3 SET_SQL_TRACE Procedure Parameters
SET_NLS procedure
This procedure sets up your national language support (NLS). It is equivalent to the following SQL statement:
Syntax
Parameters
Table 43-4 SET_NLS Procedure Parameters
NLS parameter. The parameter name must begin with 'NLS'.
CLOSE_DATABASE_LINK procedure
This procedure closes an open database link. It is equivalent to the following SQL statement:
Syntax
Parameters
Table 43-5 CLOSE_DATABASE_LINK Procedure Parameters
RESET_PACKAGE procedure
This procedure deinstantiates all packages in this session: It frees all package state.
Memory used for caching execution state is associated with all PL/SQL functions, procedures, and packages that have been run in a session.
For packages, this collection of memory holds the current values of package variables and controls the cache of cursors opened by the respective PL/SQL programs. A call to RESET_PACKAGE frees the memory associated with each of the previously run PL/SQL programs from the session, and, consequently, clears the current values of any package globals and closes any cached cursors.
RESET_PACKAGE can also be used to reliably restart a failed program in a session. If a program containing package variables fails, then it is hard to determine which variables need to be reinitialized. RESET_PACKAGE guarantees that all package variables are reset to their initial values.
Syntax
Parameters
Usage Notes
Because the amount of memory consumed by all executed PL/SQL can become large, you might use RESET_PACKAGE to trim down the session memory footprint at certain points in your database application. However, make sure that resetting package variable values will not affect the application. Also, remember that later execution of programs that have lost their cached memory and cursors will perform slower, because they need to recreate the freed memory and cursors.
RESET_PACKAGE does not free the memory, cursors, and package variables immediately when called.
RESET_PACKAGE only frees the memory, cursors, and package variables after the PL/SQL call that made the invocation finishes running.
For example, PL/SQL procedure P1 calls PL/SQL procedure P2 , and P2 calls RESET_PACKAGE . The RESET_PACKAGE effects do not occur until procedure P1 finishes execution (the PL/SQL call ends).
Example
This SQL*Plus script runs a large program with many PL/SQL program units that may or may not use global variables, but it doesn't need them beyond this execution:
To free up PL/SQL cached session memory:
To run another large program:
UNIQUE_SESSION_ID function
This function returns an identifier that is unique for all sessions currently connected to this database. Multiple calls to this function during the same session always return the same result.
Syntax
Parameters
Pragmas
Returns
Table 43-6 UNIQUE_SESSION_ID Function Returns
IS_ROLE_ENABLED function
This function determines if the named role is enabled for this session.
Syntax
Parameters
Table 43-7 IS_ROLE_ENABLED Function Parameters
Returns
Table 43-8 IS_ROLE_ENABLED Function Returns
IS_SESSION_ALIVE function
This function determines if the specified session is alive.
Syntax
Parameters
Table 43-9 IS_SESSION_ALIVE Function Parameters
Returns
Table 43-10 IS_SESSION_ALIVE Function Returns
SET_CLOSE_CACHED_OPEN_CURSORS procedure
This procedure turns close_cached_open_cursors on or off. It is equivalent to the following SQL statement:
Syntax
Parameters
Table 43-11 SET_CLOSE_CACHED_OPEN_CURSORS Procedure Parameters
FREE_UNUSED_USER_MEMORY procedure
This procedure reclaims unused memory after performing operations requiring large amounts of memory (more than 100K).
Examples of operations that use large amounts of memory include:
-
Large sorting where entire sort_area_size is used and sort_area_size is hundreds of KB.
You can monitor user memory by tracking the statistics "session uga memory" and "session pga memory" in the v$sesstat or v$statname fixed views. Monitoring these statistics also shows how much memory this procedure has freed.
This procedure should only be used in cases where memory is at a premium. It should be used infrequently and judiciously.
Syntax
Parameters
Returns
The behavior of this procedure depends upon the configuration of the server operating on behalf of the client:
-
Dedicated server : This returns unused PGA memory and session memory to the operating system. Session memory is allocated from the PGA in this configuration.
Usage Notes
In order to free memory using this procedure, the memory must not be in use.
After an operation allocates memory, only the same type of operation can reuse the allocated memory. For example, after memory is allocated for sort, even if the sort is complete and the memory is no longer in use, only another sort can reuse the sort-allocated memory. For both sort and compilation, after the operation is complete, the memory is no longer in use, and the user can call this procedure to free the unused memory.
An indexed table implicitly allocates memory to store values assigned to the indexed table's elements. Thus, the more elements in an indexed table, the more memory the RDBMS allocates to the indexed table. As long as there are elements within the indexed table, the memory associated with an indexed table is in use.
The scope of indexed tables determines how long their memory is in use. Indexed tables declared globally are indexed tables declared in packages or package bodies. They allocate memory from session memory. For an indexed table declared globally, the memory remains in use for the lifetime of a user's login (lifetime of a user's session), and is freed after the user disconnects from ORACLE.
Indexed tables declared locally are indexed tables declared within functions, procedures, or anonymous blocks. These indexed tables allocate memory from PGA memory. For an indexed table declared locally, the memory remains in use for as long as the user is still running the procedure, function, or anonymous block in which the indexed table is declared.After the procedure, function, or anonymous block is finished running, the memory is then available for other locally declared indexed tables to use (in other words, the memory is no longer in use).
Assigning an uninitialized, "empty" indexed table to an existing index table is a method to explicitly re-initialize the indexed table and the memory associated with the indexed table. After this operation, the memory associated with the indexed table is no longer in use, making it available to be freed by calling this procedure. This method is particularly useful on indexed tables declared globally which can grow during the lifetime of a user's session, as long as the user no longer needs the contents of the indexed table.
The memory rules associated with an indexed table's scope still apply; this method and this procedure, however, allow users to intervene and to explicitly free the memory associated with an indexed table.
Example
The PL/SQL fragment below illustrates the method and the use of procedure FREE_UNUSED_USER_MEMORY .
SET_CONTEXT procedure
This procedure sets or resets the value of a context attribute.
Syntax
Parameters
Table 43-12 SET_CONTEXT Procedure Parameters
Name of the namespace to use for the application context (limited to 30 bytes).
Name of the attribute to be set (limited to 30 bytes).
Usage Notes
The caller of this function must be in the calling stack of a procedure which has been associated to the context namespace through a CREATE CONTEXT statement. The checking of the calling stack does not cross DBMS boundary.
There is no limit on the number of attributes that can be set in a namespace. An attribute value remains for user session, or until it is reset by the user.
LIST_CONTEXT procedure
This procedure returns a list of active namespaces and contexts for the current session.
Syntax
Parameters
Table 43-13 LIST_CONTEXT Procedure Parameters
Returns
Table 43-14 LIST_CONTEXT Procedure Returns
A list of (namespace, attribute, values) set in current session
Usage Notes
The context information in the list appears as a series of < namespace > < attribute >< value >. Because list is a table type variable, its size is dynamically adjusted to the size of returned list.
SWITCH_CURRENT_CONSUMER_GROUP procedure
This procedure changes the current resource consumer group of a user's current session.
This lets you switch to a consumer group if you have the switch privilege for that particular group. If the caller is another procedure, then this enables the user to switch to a consumer group for which the owner of that procedure has switch privilege.
Syntax
Parameters
Table 43-15 SWITCH_CURRENT_CONSUMER_GROUP Procedure Parameters
Name of consumer group to which you want to switch.
Name of the consumer group from which you just switched out.
Returns
This procedure outputs the old consumer group of the user in the parameter old_consumer_group .
You can switch back to the old consumer group later using the value returned in old_consumer_group .
Exceptions
Table 43-16 SWITCH_CURRENT_CONSUMER_GROUP Procedure Exceptions
Non-existent consumer group.
Insufficient privileges.
Usage Notes
The owner of a procedure must have privileges on the group from which a user was switched ( old_consumer_group ) in order to switch them back. There is one exception: The procedure can always switch the user back to his/her initial consumer group (skipping the privilege check).
By setting initial_group_on_error to TRUE , SWITCH_CURRENT_CONSUMER_GROUP puts the current session into the default group, if it can't put it into the group designated by new_consumer_group . The error associated with the attempt to move a session into new_consumer_group is raised, even though the current consumer group has been changed to the initial consumer group.
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.
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.
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.
security_clause for more information on the GUARD setting
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.
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 .
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 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.
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
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.
Resumable space allocation is fully supported for operations on locally managed tablespaces. Some restrictions apply if you are using dictionary-managed tablespaces. For information on these restrictions, refer to Oracle Database Administrator's Guide .
This clause enables resumable space allocation for the session.
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 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 '.
Oracle Database Reference for information on the data dictionary views
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.
Oracle Data Guard Concepts and Administration for more information on this session parameter
Use the alter_session_set_clause to set initialization parameter values or to set an edition for the current session.
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" )
You can set values for multiple parameters in the same alter_session_set_clause .
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:
CREATE EDITION for more information on editions and Oracle Database PL/SQL Language Reference for information on how editions are designated as USABLE
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:
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.
Oracle Database Administrator's Guide for more information on switching to a container
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.
The CREATE TABLE ROW ARCHIVAL clause to learn how to enable a new table for row archival
The ALTER TABLE [NO] ROW ARCHIVAL clause to learn how to enable or disable an existing table for row archival
Oracle Database VLDB and Partitioning Guide for more information on In-Database Archiving
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:
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 .
The DEFAULT COLLATION Clause clause of CREATE USER for more information on the default collation of a schema
The effective schema default collation for a session should not be confused with the session parameter NLS_SORT . The effective schema default collation is used by DDL statements to decide the default data-bound collation of tables, views, and materialized views when they are created. The session parameter NLS_SORT points to a named collation that is used when Oracle executes a query, a DML statement, or PL/SQL code containing a SQL operation whose determined collation is a pseudo-collation, such as USING_NLS_COMP or USING_NLS_SORT . Refer to Oracle Database Globalization Support Guide for more information.
Initialization Parameters and ALTER SESSION
Some initialization parameter are dynamic in the scope of ALTER SESSION . When you set these parameters using ALTER SESSION , the value you set persists only for the duration of the current session.To determine whether a parameter can be altered using an ALTER SESSION statement, query the ISSES_MODIFIABLE column of the V$PARAMETER dynamic performance view.
Before changing the values of initialization parameters, refer to their full description in Oracle Database Reference .
A number of parameters that can be set using ALTER SESSION are not initialization parameters. You can set them only with ALTER SESSION , not in an initialization parameter file. Those session parameters are described in "Session Parameters and ALTER SESSION" .
Session Parameters and ALTER SESSION
The following parameters are session parameters only, not initialization parameters:
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.
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.
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.
security_clause for more information on the GUARD setting
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.
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 .
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 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.
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
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.
Resumable space allocation is fully supported for operations on locally managed tablespaces. Some restrictions apply if you are using dictionary-managed tablespaces. For information on these restrictions, refer to Oracle Database Administrator's Guide .
This clause enables resumable space allocation for the session.
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 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 '.
Oracle Database Reference for information on the data dictionary views
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.
Oracle Data Guard Concepts and Administration for more information on this session parameter
Use the alter_session_set_clause to set initialization parameter values or to set an edition for the current session.
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" )
You can set values for multiple parameters in the same alter_session_set_clause .
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:
CREATE EDITION for more information on editions and Oracle Database PL/SQL Language Reference for information on how editions are designated as USABLE
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:
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.
Oracle Database Administrator's Guide for more information on switching to a container
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.
The CREATE TABLE ROW ARCHIVAL clause to learn how to enable a new table for row archival
The ALTER TABLE [NO] ROW ARCHIVAL clause to learn how to enable or disable an existing table for row archival
Oracle Database VLDB and Partitioning Guide for more information on In-Database Archiving
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:
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 .
The DEFAULT COLLATION Clause clause of CREATE USER for more information on the default collation of a schema
The effective schema default collation for a session should not be confused with the session parameter NLS_SORT . The effective schema default collation is used by DDL statements to decide the default data-bound collation of tables, views, and materialized views when they are created. The session parameter NLS_SORT points to a named collation that is used when Oracle executes a query, a DML statement, or PL/SQL code containing a SQL operation whose determined collation is a pseudo-collation, such as USING_NLS_COMP or USING_NLS_SORT . Refer to Oracle Database Globalization Support Guide for more information.
Initialization Parameters and ALTER SESSION
Some initialization parameter are dynamic in the scope of ALTER SESSION . When you set these parameters using ALTER SESSION , the value you set persists only for the duration of the current session.To determine whether a parameter can be altered using an ALTER SESSION statement, query the ISSES_MODIFIABLE column of the V$PARAMETER dynamic performance view.
Before changing the values of initialization parameters, refer to their full description in Oracle Database Reference .
A number of parameters that can be set using ALTER SESSION are not initialization parameters. You can set them only with ALTER SESSION , not in an initialization parameter file. Those session parameters are described in "Session Parameters and ALTER SESSION" .
Session Parameters and ALTER SESSION
The following parameters are session parameters only, not initialization parameters:
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.
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.
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.
security_clause for more information on the GUARD setting
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.
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 .
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 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.
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
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.
Resumable space allocation is fully supported for operations on locally managed tablespaces. Some restrictions apply if you are using dictionary-managed tablespaces. For information on these restrictions, refer to Oracle Database Administrator's Guide .
This clause enables resumable space allocation for the session.
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 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 '.
Oracle Database Reference for information on the data dictionary views
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.
Oracle Data Guard Concepts and Administration for more information on this session parameter
Use the alter_session_set_clause to set initialization parameter values or to set an edition for the current session.
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" )
You can set values for multiple parameters in the same alter_session_set_clause .
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:
CREATE EDITION for more information on editions and Oracle Database PL/SQL Language Reference for information on how editions are designated as USABLE
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:
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.
Oracle Database Administrator's Guide for more information on switching to a container
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.
The CREATE TABLE ROW ARCHIVAL clause to learn how to enable a new table for row archival
The ALTER TABLE [NO] ROW ARCHIVAL clause to learn how to enable or disable an existing table for row archival
Oracle Database VLDB and Partitioning Guide for more information on In-Database Archiving
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:
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 .
The DEFAULT COLLATION Clause clause of CREATE USER for more information on the default collation of a schema
The effective schema default collation for a session should not be confused with the session parameter NLS_SORT . The effective schema default collation is used by DDL statements to decide the default data-bound collation of tables, views, and materialized views when they are created. The session parameter NLS_SORT points to a named collation that is used when Oracle executes a query, a DML statement, or PL/SQL code containing a SQL operation whose determined collation is a pseudo-collation, such as USING_NLS_COMP or USING_NLS_SORT . Refer to Oracle Database Globalization Support Guide for more information.
Initialization Parameters and ALTER SESSION
Some initialization parameter are dynamic in the scope of ALTER SESSION . When you set these parameters using ALTER SESSION , the value you set persists only for the duration of the current session.To determine whether a parameter can be altered using an ALTER SESSION statement, query the ISSES_MODIFIABLE column of the V$PARAMETER dynamic performance view.
Before changing the values of initialization parameters, refer to their full description in Oracle Database Reference .
A number of parameters that can be set using ALTER SESSION are not initialization parameters. You can set them only with ALTER SESSION , not in an initialization parameter file. Those session parameters are described in "Session Parameters and ALTER SESSION" .
Session Parameters and ALTER SESSION
The following parameters are session parameters only, not initialization parameters:
Читайте также:
- Буквы кириллицы неправильно отображаются в имени детали после импорта файла step в inventor
- Gaomon pd1161 ips hd обзор
- Когда появится xbox series x в казахстане
- Uno id что за файл
- В качестве имени файла можно использовать последовательность символов