Release 1 (9.0.1)
Part Number A90125-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER SESSION to ALTER SYSTEM, 2 of 3
Use the ALTER SESSION statement 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.
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.
alter_session::=
alter_session
alter_session_set_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, 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.
Specify CLOSE DATABASE LINK to close 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.
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, 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 DISABLE COMMIT IN PROCEDURE.
Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation for more information.
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.
The session's DML statements are executed in parallel mode if a parallel hint or a parallel clause is specified.
The session's DDL statements are executed in parallel mode if a parallel clause is specified.
The session's queries are executed in parallel mode if a parallel hint or a parallel clause is specified
Restriction: You cannot specify the optional PARALLEL integer with ENABLE.
Specify DISABLE to execute subsequent statements serially. This is the default for DML statements.
The session's DML statements are executed serially.
The session's DDL statements are executed serially.
The session's queries are executed 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.
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.
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.
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.
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:
FORCE DDL, the degree overrides any parallel clause in subsequent DDL statements.
FORCE DML and QUERY, the degree overrides the degree currently stored for the table in the data dictionary.
The following types of DML operations are not parallelized regardless of this clause:
LONG or LOB datatypes.
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.
|
Note: 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, please refer to Oracle9i 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, Oracle 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 inserts the text string into the USER_RESUMABLE and DBA_RESUMABLE data dictionary views. If you do not specify NAME, Oracle inserts the default string 'User username(userid), Session sessionid, Instance instanceid'.
This clause disables resumable space allocation for the session.
Use the alter_session_set_clause to set the parameters that follow (session parameters and initialization parameters that are dynamic in the scope of the ALTER SESSION statement). You can set values for multiple parameters in the same alter_session_set_clause.
COMMENT lets you associate a comment string with this change in the value of the parameter.
All initialization parameters that can be set using an ALTER SYSTEM statement are documented at ALTER SYSTEM. The initialization parameters that are dynamic in the scope of ALTER SESSION are listed in Table 9-1 with cross-references to their descriptions in ALTER SYSTEM. The only difference in behavior is that when you set these parameters using ALTER SESSION, the value you set persists only for the duration of the current session.
A number of parameters that can be set using ALTER SESSION are not initialization parameters. That is, you can set them only with ALTER SESSION, not in an initialization parameter file. Those session parameters are described after Table 9-1.
|
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 Oracle9i Database Reference or Oracle9i Globalization Support Guide. |
| Parameter | Comments |
|---|---|
|
See also Oracle9i Database Performance Guide and Reference for information on setting this parameter in these and other environments. |
|
|
The setting made by |
|
|
|
|
|
|
|
|
|
|
|
See "Referring to Objects in Remote Databases" and Oracle9i Distributed Database Systems for more information on global name resolution and how Oracle enforces it. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
When you start an instance, Oracle establishes globalization support based on the values of initialization parameters that begin with "NLS". You can query the dynamic performance table |
|
|
|
|
|
|
|
|
|
|
|
See "Date Format Models" for information on valid date format models. |
|
|
|
|
|
See "Number Format Models" for information on number format elements. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
See Oracle9i Database Concepts and Oracle9i Database Performance Guide and Reference for information on how to choose a goal for the cost-based approach based on the characteristics of your application. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For important information on partition views, see "Partition Views". |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
The following parameters are session parameters only, not initialization parameters:
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.
Syntax:
CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name'}
The CREATE_STORED_OUTLINES parameter determines whether Oracle should automatically create and store an outline for each query submitted during the session.
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.
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 you any additional system or object privileges for the session.
Syntax:
ERROR_ON_OVERLAP_TIME = {TRUE | FALSE}
The ERROR_ON_OVERLAP_TIME determines how Oracle should handle an ambiguous boundary datetime value--that is, a case in which it is not clear whether the datetime is in standard or daylight savings time.
TRUE to return an error for the ambiguous overlap timestamp.
FALSE to default the ambiguous overlap timestamp to the standard time. This is the default.
Syntax:
FLAGGER = { ENTRY | INTERMEDIATE | FULL | OFF }
The FLAGGER parameter 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.
Syntax:
INSTANCE = integer
The INSTANCE parameter in a Real Application Clusters environment accesses database files as if the session were connected to the instance specified by integer. INSTANCE is a session parameter only, not an initialization parameter. For optimum performance, each instance of Real Application Clusters uses its own private rollback segments, freelist groups, and so on. In a Real Application Clusters environment, you normally connect to a particular instance and access data that is partitioned primarily for your use. If you must connect to another instance, the data partitioning can be lost. Setting this parameter lets you access an instance as if you were connected to your own instance.
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 SQL92. That is, 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 transaction behavior. Thus, 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.
Syntax:
PLSQL_DEBUG = { TRUE | FALSE }
The PLSQL_DEBUG parameter sets the default for including or not including debugging information during compile operations. Setting this parameter to TRUE has the same effect as adding the DEBUG keyword to ALTER {FUNCTION | PROCEDURE | PACKAGE} COMPILE statements.
Syntax:
SKIP_UNUSABLE_INDEXES = { TRUE | FALSE }
The SKIP_UNUSABLE_INDEXES parameter controls the use and reporting of tables with unusable indexes or index partitions. SKIP_UNUSABLE_INDEXES is a session parameter only, not an initialization parameter.
TRUE disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.
FALSE enables error reporting of indexes marked UNUSABLE. This setting does not allow inserts, deletes, and updates on tables with unusable indexes or index partitions. This is the default.
Syntax:
INSTANCE = integer
SQL_TRACE is an initialization parameter. However, when you change its value with an ALTER SESSION statement, the results are not reflected in the V$PARAMETER view. Therefore, in this context it is considered a session parameter only.
See Oracle9i Database Performance Guide and Reference for more information on the SQL trace facility, including how to format and interpret its output.
Syntax:
TIME_ZONE = '[+ | -] hh:mm' | LOCAL | DBTIMEZONE | 'time_zone_region'
The TIME_ZONE parameter specifies the default local time zone displacement for the current SQL session. TIME_ZONE is a session parameter only, not an initialization parameter.
'[+|-]hh:mm') indicating the hours and minutes before or after UTC (Coordinated Universal Time--formerly Greenwich Mean Time). The valid range for hh:mm is -12:00 to +14:00.
LOCAL to set the default local time zone displacement of the current SQL session to the original default local time zone displacement that was established when the current SQL session was started.
DBTIMEZONE to set the current session time zone to match the value set for the database time zone. If you specify this setting, the DBTIMEZONE function will return the database time zone as a UTC offset or a timezone region, depending on how the database time zone has been set.
time_zone_region. To see a listing of valid region names, query the TZNAME column of the V$TIMEZONE_NAMES dynamic performance view. If you specify this setting, the SESSIONTIMEZONE function will return the region name.
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, 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, 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: You cannot enable this parameter if USE_STORED_OUTLINES is enabled.
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: You cannot enable this parameter if USE_PRIVATE_OUTLINES is enabled.
Issue the following statement to enable parallel DML mode for the current session:
ALTER SESSION ENABLE PARALLEL DML;
The following transaction inserts an employee record into the employees table on the database identified by the database link site1 and deletes an employee record from the employees table on the database identified by site2:
ALTER SESSION ADVISE COMMIT; INSERT INTO employees@site1 VALUES (8002, 'Juan', 'Fernandez', 'juanf@hr.com', NULL, TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SA_CLERK', 3000, NULL, 121, 20); ALTER SESSION ADVISE ROLLBACK; DELETE FROM employees@site2 WHERE employee_id = 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.
This statement updates the employee table on the hq database using a database link, commits the transaction, and explicitly closes the database link:
UPDATE employees@hq SET salary = salary + 200 WHERE employee_id = 162; COMMIT; ALTER SESSION CLOSE DATABASE LINK hq;
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 ------------------- 2001 04 12 12:30:38
The following statement changes the language for date format elements to French:
ALTER SESSIONSET NLS_DATE_LANGUAGE = French;SELECT TO_CHAR(SYSDATE, 'Day DD Month YYYY') TodayFROM DUAL;TODAY---------------------------Jeudi 12 Avril 2001
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
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:
ALTER SESSION SET NLS_CURRENCY = 'FF'; SELECT TO_CHAR( SUM(salary), 'L999G999D99') Total FROM employees; TOTAL --------------------- FF694.900,00
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
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
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.
To enable the SQL trace facility for your session, issue the following statement:
ALTER SESSION SET SQL_TRACE = TRUE;
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;
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|