The following parameters are session parameters only, not initialization parameters:
CONSTRAINT[S]
Syntax:
CONSTRAINT[S] = { IMMEDIATE | DEFERRED | DEFAULT }
The CONSTRAINT[S] parameter determines when conditions specified by a deferrable constraint are enforced.
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.
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.
DEFAULT restores all constraints at the beginning of each transaction to their initial state of DEFERRED or IMMEDIATE.
CURRENT_SCHEMA
Syntax:
CURRENT_SCHEMA = schema
The CURRENT_SCHEMA parameter 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.
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 the session user any additional system or object privileges for the session.
ERROR_ON_OVERLAP_TIME
Syntax:
ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}
The ERROR_ON_OVERLAP_TIME parameter determines how Oracle Database should handle an ambiguous boundary datetime valueâa case in which it is not clear whether the datetime is in standard or daylight saving time.
Specify TRUE to return an error for the ambiguous overlap timestamp.
Specify FALSE to default the ambiguous overlap timestamp to the standard time. This is the default.
Refer to "Support for Daylight Saving Times" for more information on boundary datetime values.
FLAGGER
Syntax:
FLAGGER = { ENTRY | OFF }
The FLAGGER parameter specifies FIPS flagging (as specified in Federal Information Processing Standard 127-2), which causes an error message to be generated when a SQL statement issued is an extension of the Entry Level of SQL-92 (officially, ANSI X3.135-1992, a standard that is now superseded by SQL:2016). FLAGGER is a session parameter only, not an initialization parameter.
After 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.
INSTANCE
Syntax:
INSTANCE = integer
Setting the INSTANCE parameter lets you access another instance as if you were connected to your own instance. INSTANCE is a session parameter only, not an initialization parameter. In an Oracle Real Application Clusters (Oracle RAC) environment, each Oracle RAC instance retains static or dynamic ownership of disk space for optimal DML performance based on the setting of this parameter.
ISOLATION_LEVEL
Syntax:
ISOLATION_LEVEL = {SERIALIZABLE | READ COMMITTED}
The ISOLATION_LEVEL parameter specifies how transactions containing database modifications are handled. ISOLATION_LEVEL is a session parameter only, not an initialization parameter.
SERIALIZABLE indicates that transactions in the session use the serializable transaction isolation mode as specified in the SQL standard. If a serializable transaction attempts to execute a DML statement that updates rows currently being updated by another uncommitted transaction at the start of the serializable transaction, then the DML statement fails. A serializable transaction can see its own updates.
READ COMMITTED indicates that transactions in the session will use the default Oracle Database transaction behavior. If the transaction contains DML that requires row locks held by another transaction, then the DML statement will wait until the row locks are released.
Note:
Serializable transactions do not work with deferred segment creation or interval partitioning. Trying to insert data into an empty table with no segment created, or into a partition of an interval partitioned table that does not yet have a segment, causes an error.
STANDBY_MAX_DATA_DELAY
Syntax:
STANDBY_MAX_DATA_DELAY = { integer | NONE }
In an Active Data Guard environment, this session parameter can be used to specify a session-specific apply lag tolerance, measured in seconds, for queries issued by non-administrative users to a physical standby database that is in real-time query mode. This capability allows queries to be safely offloaded from the primary database to a physical standby database, because it is possible to detect if the standby database has become unacceptably stale.
If STANDBY_MAX_DATA_DELAY is set to the default value of NONE, queries issued to a physical standby database will be executed regardless of the apply lag on that database.
If STANDBY_MAX_DATA_DELAY is set to a nonzero value, a query issued to a physical standby database will be executed only if the apply lag is less than or equal to STANDBY_MAX_DATA_DELAY. Otherwise, an ORA-3172 error is returned to alert the client that the apply lag is too large.
If STANDBY_MAX_DATA_DELAY is set to 0, a query issued to a physical standby database is guaranteed to return the exact same result as if the query were issued on the primary database, unless the standby database is lagging behind the primary database, in which case an ORA-3172 error is returned.
TIME_ZONE
Syntax:
TIME_ZONE = '[+ | -] hh:mi'
| LOCAL
| DBTIMEZONE
| 'time_zone_region'
The TIME_ZONE parameter specifies the default local time zone offset or region name for the current SQL session. TIME_ZONE is a session parameter only, not an initialization parameter. To determine the time zone of the current session, query the built-in function SESSIONTIMEZONE (see SESSIONTIMEZONE).
Specify a format mask ('[+|-]hh:mi') indicating the hours and minutes before or after UTC (Coordinated Universal Timeâformerly Greenwich Mean Time). The valid range for hh:mi is -12:00 to +14:00.
Specify LOCAL to set the default local time zone offset of the current SQL session to the original default local time zone offset that was established when the current SQL session was started.
Specify DBTIMEZONE to set the current session time zone to match the value set for the database time zone. If you specify this setting, then the DBTIMEZONE function will return the database time zone as a UTC offset or a time zone region, depending on how the database time zone has been set.
Specify a valid time_zone_region. To see a listing of valid time zone region names, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view. If you specify this setting, then the SESSIONTIMEZONE function will return the region name.
Note:
Time zone region names are needed by the daylight saving feature. These names are stored in two types of time zone files: one large and one small. One of these files is the default file, depending on your environment and the release of Oracle Database you are using. For more information regarding time zone files and names, see Oracle Database Globalization Support Guide.
USE_PRIVATE_OUTLINES
Syntax:
USE_PRIVATE_OUTLINES = { TRUE | FALSE | category_name }
The USE_PRIVATE_OUTLINES parameter lets you control the use of private outlines. When this parameter is enabled and an outlined SQL statement is issued, the optimizer retrieves the outline from the session private area rather than the public area used when USE_STORED_OUTLINES is enabled. If no outline exists in the session private area, then the optimizer will not use an outline to compile the statement. USE_PRIVATE_OUTLINES is not an initialization parameter.
TRUE causes the optimizer to use private outlines stored in the DEFAULT category when compiling requests.
FALSE specifies that the optimizer should not use stored private outlines. This is the default. If USE_STORED_OUTLINES is enabled, then the optimizer will use stored public outlines.
category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.
Restriction on USE_PRIVATE_OUTLINES
You cannot enable this parameter if USE_STORED_OUTLINES is enabled.
USE_STORED_OUTLINES
Note:
Stored outlines are deprecated. They are still supported for backward compatibility. However, Oracle recommends that you use SQL plan management instead. Refer to Oracle Database SQL Tuning Guide for more information about SQL plan management.
Syntax:
USE_STORED_OUTLINES = { TRUE | FALSE | category_name }
The USE_STORED_OUTLINES parameter determines whether the optimizer will use stored public outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.
TRUE causes the optimizer to use outlines stored in the DEFAULT category when compiling requests.
FALSE specifies that the optimizer should not use stored outlines. This is the default.
category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.
Restriction on USED_STORED_OUTLINES
You cannot enable this parameter if USE_PRIVATE_OUTLINES is enabled.
Examples
Enabling Parallel DML: Example
Issue the following statement to enable parallel DML mode for the current session:
ALTER SESSION ENABLE PARALLEL DML;
Forcing a Distributed Transaction: Example
The following transaction inserts an employee record into the employees table on the database identified by the database link remote and deletes an employee record from the employees table on the database identified by local:
ALTER SESSION
ADVISE COMMIT;
INSERT INTO employees@remote
VALUES (8002, 'Juan', 'Fernandez', 'juanf@example.com', NULL,
TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SA_CLERK', 3000,
NULL, 121, 20);
ALTER SESSION
ADVISE ROLLBACK;
DELETE FROM employees@local
WHERE employee_id = 8002;
COMMIT;
This transaction has two ALTER SESSION statements with the ADVISE clause. If the transaction becomes in doubt, then remote is sent the advice 'COMMIT' by virtue of the first ALTER SESSION statement and local is sent the advice 'ROLLBACK' by virtue of the second statement.
Closing a Database Link: Example
This statement updates the jobs table on the local database using a database link, commits the transaction, and explicitly closes the database link:
UPDATE jobs@local SET min_salary = 3000 WHERE job_id = 'SH_CLERK'; COMMIT; ALTER SESSION CLOSE DATABASE LINK local;
Changing the Date Format Dynamically: 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 Database uses the new default date format:
SELECT TO_CHAR(SYSDATE) Today FROM DUAL; TODAY ------------------- 2001 04 12 12:30:38
Changing the Date Language Dynamically: 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 --------------------------- Jeudi 12 Avril 2001
Changing the 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(salary), 'C999G999D99') Total
FROM employees;
TOTAL
------------------
USD694,900.00
Changing the 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 Database returns these new characters when you use their number format elements:
ALTER SESSION SET NLS_CURRENCY = 'FF';
SELECT TO_CHAR( SUM(salary), 'L999G999D99') Total FROM employees;
TOTAL
---------------------
FF694.900,00
Changing the NLS Currency: Example
The following statement dynamically changes the local currency symbol to 'DM':
ALTER SESSION
SET NLS_CURRENCY = 'DM';
SELECT TO_CHAR( SUM(salary), 'L999G999D99') Total
FROM employees;
TOTAL
---------------------
DM694.900,00
Changing the NLS Language: Example
The following statement dynamically changes to French the language in which error messages are displayed:
ALTER SESSION SET NLS_LANGUAGE = FRENCH; Session modifiee. SELECT * FROM DMP; ORA-00942: Table ou vue inexistante
Changing the Linguistic Sort Sequence: Example
The following statement dynamically changes the linguistic sort sequence to Spanish:
ALTER SESSION SET NLS_SORT = XSpanish;
Oracle Database sorts character values based on their position in the Spanish linguistic sort sequence.
Enabling 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;
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4