To raise your session label, you must have WRITEUP and READUP system privileges. To lower your session label, you must have WRITEDOWN system privilege. To change your session label laterally, you must have READUP, WRITEUP, and WRITEDOWN system privileges.
To perform the other operations of this command, you do not need any privileges.
TRUE enables the SQL trace facility.
FALSE disables the SQL trace facility.
GLOBAL_NAMES
controls the enforcement of global name resolution for your session:
TRUE enables the enforcement of global name resolution.
FALSE disables the enforcement of global name resolution.
For information on enabling and disabling global name resolution with this parameter, see the ALTER SYSTEM command ![[*]](jump.gif)
.
NLS_LANGUAGE
changes the language in which Oracle7 returns errors and other messages. This parameter also implicitly specifies new values for these items:
NLS_TERRITORY
NLS_DATE_FORMAT
explicitly specifies a new default date format. The 'fmt' value must be a date format model as specified in the section "Date Format" .
NLS_DATE_LANGUAGE
NLS_NUMERIC_CHARACTERS
explicitly specifies a new decimal character and group separator. The 'text' value must have this form:
'dg'
where:
d is the new decimal character.
g is the new group separator.
The decimal character and the group separator must be two different single-byte characters, and cannot be a numeric value or any of the following characters:
"+" plus "-" minus (or hyphen) "<" less-than ">" greater-than
NLS_ISO_CURRENCY
explicitly specifies the territory whose ISO currency symbol should be used.
NLS_CURRENCY
explicitly specifies a new local currency symbol. The symbol cannot exceed 10 characters.
NLS_SORT
changes the sequence into which Oracle7 sorts character values.
sort specifies the name of a linguistic sort sequence.
BINARY specifies a binary sort.
The default sort for all character sets is binary.
NLS_CALENDAR
explicitly specifies a new calendar type.
LABEL
changes your DBMS session label to either:
MLS_LABEL_FORMAT
changes the default label format for your session. For more information on this parameter, see the Trusted Oracle7 Server Administrator's Guide.
OPTIMIZER_GOAL
specifies the approach and goal of the optimizer for your session:
RULE specifies the rule-based approach.
ALL_ROWS specifies the cost-based approach and optimizes for best throughput.
FIRST_ROWS specifies the cost-based approach and optimizes for best response time.
CHOOSE causes the optimizer to choose an optimization approach based on the presence of statistics in the data dictionary.
FLAGGER
ENTRY flags for SQL92 Entry level
INTERMEDIATE
flags for SQL92 Intermediate level
FULL flags for SQL92 Full level
OFF turns off flagging
SESSION_CACHED_CURSORS
specify the size of the session cache for holding frequently used cursors.
integer specifies how many cursors can be retained in the cache.
CLOSE_OPEN_CACHED_CURSORS
controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK.
INSTANCE
HASH_JOIN_ENABLED
enables or disables the use of the hash join operation in queries. The default is TRUE, which allows hash joins.
HASH_AREA_SIZE
specifies in bytes the amount of memory to use for hash join operations. The default is twice the value of the SORT_AREA_SIZE initialization parameter.
HASH_MULTIBLOCK_IO_COUNT
specifies the number of data blocks to read and write during a hash join operation. The value multiplied by the DB_BLOCK_SIZE initialization parameter should not exceed 64 kilobytes. The default value for this parameter is 1. If the multi-threaded server is used, the value is always 1, and any value given here is ignored.
REMOTE_DEPENDENCIES_MODE
specifies how dependencies of remote stored procedures are handled by the session. For more information, refer to "Remote Dependencies" in the Oracle7 Server Application Developer's Guide.
ISOLATION_LEVEL
specifies how transactions containing database modifications are handled.
SERIALIZABLE
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 that are 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. The COMPATIBLE initialization parameter must be set to 7.3.0 or higher for SERIALIZABLE mode to work.
READ COMMITTED
transactions in the session will use the default Oracle transaction behavior. Thus, if the transaction contains DML that require row locks held by another transaction, then the DML statement will wait until the row locks are released.
CLOSE DATABASE LINK
closes the database link dblink, eliminating your session's connection to the remote database. The database link cannot be currently in use by an active transaction or an open cursor.
ADVISE
COMMIT places the value 'C' in DBA_2PC_PENDING.ADVICE.
ROLLBACK places the value 'R' in DBA_2PC_PENDING.ADVICE.
NOTHING places the value ' ' in DBA_2PC_PENDING.ADVICE.
COMMIT IN PROCEDURE
specifies whether procedures and stored functions can issue COMMIT and ROLLBACK statements:
ENABLE permits procedures and stored functions to issue these statements.
DISABLE prohibits procedures and stored functions from issuing these statements.
For more information on the SQL trace facility, including how to format and interpret its output, see Appendix A "Performance Diagnostic Tools" of the Oracle7 Server Tuning.
Example I
To enable the SQL trace facility for your session, issue the following statement:
ALTER SESSION SET SQL_TRACE = TRUE
Example II
The following statement changes the language for error messages to the French:
ALTER SESSION SET NLS_LANGUAGE = French
Oracle7 returns error messages in French:
SELECT * FROM emp ORA-00942: Table ou vue n'existe pas
. Example III
ALTER SESSION
SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
Oracle7 uses the new default date format:
SELECT TO_CHAR(SYSDATE) Today FROM DUAL TODAY ------------------- 1993 08 12 14:25:56
Example IV
The following statement changes the language for date format elements to the French:
ALTER SESSION SET NLS_DATE_LANGUAGE = French SELECT TO_CHAR(SYSDATE, 'Day DD Month YYYY') Today FROM DUAL TODAY --------------------------- Mardi 28 Février 1992
D (decimal character)
is the character that separates the integer and decimal portions of a number.
G (group separator)
is the character that separates groups of digits in the integer portion of a number.
For information on how to use number format models, see the section "Number Format Models" .
The decimal character and the group separator can only be single-byte characters and cannot be the same character. If the decimal character is not a period (.), you must use single quotation marks to enclose all number values that appear in expressions in your SQL statements. When not using a period for the decimal point, you should always use the TO_NUMBER function to ensure that a valid number is retrieved.
Example V
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.'
Oracle7 returns these new characters when you use their number format elements:
SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp TOTAL ------------- FF29.025,00
Example VI
ALTER SESSION SET NLS_ISO_CURRENCY = America
SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp TOTAL ------------- USD29,025.00
Example VII
The following statement dynamically changes the local currency symbol to 'DM':
ALTER SESSION SET NLS_CURRENCY = 'DM' SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp TOTAL ------------- DM29.025,00
Example VIII
The following statement dynamically changes the linguistic sort sequence to Spanish:
ALTER SESSION
SET NLS_SORT = XSpanish
Oracle7 sorts character values based on their position in the Spanish linguistic sort sequence.
rule-based
The optimizer optimizes a SQL statement based on the indexes and clusters associated with the accessed tables, the syntactic constructs of the statement, and a heuristically ranked list of these constructs.
cost-based
The optimizer optimizes a SQL statement by considering statistics describing the tables, indexes, and clusters accessed by the statement as well as the information considered with the rule-based approach.
With the cost-based approach, the optimizer can optimize a SQL statement with one of these goals:
best throughput
or the minimal time necessary to return all rows accessed by the statement
best response time
or the minimal time necessary to return the first row accessed by the statement
When you start your instance, the optimization approach is established by the initialization parameter OPTIMIZER_MODE. If this parameter establishes the cost-based approach, the default goal is best throughput. You can subsequently change the optimization approach or the goal of the cost-based optimization approach for your session with the OPTIMIZER_GOAL parameter.
Example IX
The following statement changes the goal of the cost-based approach to best response time:
ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS
For information on how to choose a goal for the cost-based approach based on the characteristics of your application, see the Oracle7 Server Tuning.
Oracle7 uses the shared SQL area to determine if more than three parse requests were issued on a given statement. If so, Oracle7 moves the cursor into the session cursor cache. Subsequent requests to parse that SQL statement by the same session will find the cursor in the session cursor cache.
Session cursors are automatically cached if the initialization parameter, SESSION_CACHED_CURSORS is set to a positive value. This parameter specifies the maximum number of session cursors to be kept in the cache. A least recently used algorithm ages out entries in the cache to make room for new entries when needed. You use the ALTER SESSION SET SESSION_CACHED_CURSORS command to dynamically enable session cursor caching.
For more information on session cursor caching, see the Oracle7 Server Tuning.
You can use the CLOSE DATABASE LINK clause of the ALTER SESSION command to explicitly close a database link if you do not plan to use it again in your session. You may want to explicitly close a database link if the network overhead associated with leaving it open is costly. Before closing a database link, you must first close all cursors that use the link and then end your current transaction if it uses the link.
Example X
This example updates the employee table on the SALES database using a database link, commits the transaction, and explicitly closes the database link:
UPDATE emp@sales
SET sal = sal + 200
WHERE empno = 9001
COMMIT
ALTER SESSION
CLOSE DATABASE LINK sales
Before committing a distributed transaction, you can use the ADVISE clause of the ALTER SESSION command to send advice to a remote database in the event a distributed transaction becomes in-doubt. If the transaction becomes in-doubt, the advice appears in the ADVICE column of the DBA_2PC_PENDING view on the remote database. The administrator of that database can then use this advice to decide whether to commit or roll back the transaction on the remote database. For more information on distributed transactions and how to decide whether to commit or roll back in-doubt distributed transactions, see the "Database Administration" chapter of Oracle7 Server Distributed Systems, Volume I.
You issue 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. This allows you to send different advice to different databases.
Example XI
ALTER SESSION
ADVISE COMMIT
INSERT INTO emp@site1
VALUES (8002, 'FERNANDEZ', 'ANALYST', 7566,
TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 3000, NULL, 20)
ALTER SESSION
ADVISE ROLLBACK
DELETE FROM emp@site2
WHERE empno = 8002
COMMIT
This transaction has two ALTER SESSION statements with the ADVISE clause. If the transaction becomes in-doubt, SITE1 is sent the advice 'COMMIT' by virtue of the first ALTER SESSION statement and SITE2 is sent the advice 'ROLLBACK' by virtue of the second.
ALTER SESSION DISABLE COMMIT IN PROCEDURE
If you subsequently call a procedure or a stored function that issues a COMMIT or ROLLBACK statement, Oracle7 returns an error and does not commit or roll back the transaction. SQL*Forms automatically prohibits COMMIT and ROLLBACK statements in procedures and stored functions.
You can subsequently allow procedures and stored functions to issue COMMIT and ROLLBACK statements in your session by issuing the following statement:
ALTER SESSION ENABLE COMMIT IN PROCEDURE
This command does not apply to database triggers. Triggers can never issue COMMIT or ROLLBACK statements.