ALTER SESSION

Purpose

To alter your current session in one of the following ways:

• enable or disable the SQL trace facility
• enable or disable global name resolution
• change the values of NLS parameters
• specify the size of the cache used to hold frequently used cursors
• enable or disable the closing of cached cursors on COMMIT or ROLLBACK
• in a parallel server, indicate that the session must access database files as if the session were connected to another instance
• enable, disable, and change the behavior of hash join operations
• change the handling of remote procedure call dependencies
• change transaction level handling
• close a database link
• send advice to remote databases to force an in-doubt distributed transaction
• permit or prohibit stored procedures and functions from issuing COMMIT and ROLLBACK statements
• change the goal of the cost-based optimization approach
• in a parallel server, enable DML statements to be considered for parallel execution
• to insert, update, or delete from tables with indexes or index partitions marked as unusable
• allow deferrable constraints to be checked either immediately following every DML statement or at the end of a transaction

Prerequisites

To enable and disable the SQL trace facility or to change the default label format, you must have ALTER SESSION system privilege.

To perform the other operations of this command, you do not need any privileges.

Syntax

Keywords and Parameters

dg'

Enabling and Disabling the SQL Trace Facility

The SQL trace facility generates performance statistics for the processing of SQL statements. You can enable and disable the SQL trace facility for all sessions on an Oracle instance with the initialization parameter SQL_TRACE. When you begin a session, Oracle enables or disables the SQL trace facility based on the value of this parameter. You can subsequently enable or disable the SQL trace facility for your own session with the SQL_TRACE option of the ALTER SESSION command.

For more information on the SQL trace facility, including how to format and interpret its output, see Oracle8 Tuning.

Example I

To enable the SQL trace facility for your session, issue the following statement:

ALTER SESSION 
SET SQL_TRACE = TRUE;

Using NLS Parameters

Oracle contains support for use in different nations and with different languages. When you start an instance, Oracle establishes support based on the values of initialization parameters that begin with "NLS". For information on these parameters, see Oracle8 Reference. You use the NLS clauses of the ALTER SESSION command to change NLS characteristics dynamically for your session. You can query the dynamic performance table V$NLS_PARAMETERS to see the current NLS attributes for your session. The sections that follow describe the use of specific NLS parameters.

Language for Error Messages

You can specify a new language for error messages with the NLS_LANGUAGE parameter. Note that this parameter also implicitly changes other language-related items. Oracle provides error messages in a wide range of languages on many platforms.

Example II

The following statement changes the language for error messages to the French:

ALTER SESSION 
  SET NLS_LANGUAGE = French 

Oracle returns error messages in French:

SELECT * FROM emp
ORA-00942: Table ou vue n'existe pas

Default Date Format

You can specify a new default date format either explicitly with the NLS_DATE_FORMAT parameter or implicitly with the NLS_TERRITORY parameter. For information on the default date format models, see the section "Date Format Models".

Example III

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 
------------------- 
1997 08 12 14:25:56

Language for Months and Days

You can specify a new language for names and abbreviations of months and days either explicitly with the NLS_DATE_LANGUAGE parameter or implicitly with the NLS_LANGUAGE parameter.

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   1997

Decimal Character and Group Separator

You can specify new values for these number format elements either explicitly with the NLS_NUMERIC_CHARACTERS parameter or implicitly with the NLS_TERRITORY parameter:

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 "Number Format Models".

The decimal character and the group separator must be single-byte character and cannot be the same character. If the decimal character is not a period (.), you must use single quotation marks to enclose 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

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:

SELECT TO_CHAR( SUM(sal), 'L999G999D99') Total FROM emp ;

TOTAL 
-------------
FF29.025,00

ISO Currency Symbol

You can specify a new value for the C number format element (the ISO currency symbol) either explicitly with the NLS_ISO_CURRENCY parameter or implicitly with the NLS_TERRITORY parameter. The value that you specify for these parameters is a territory whose ISO currency symbol becomes the value of the C number format element.

Example VI

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(sal), 'L999G999D99') Total
FROM emp; 

TOTAL 
-------------
USD29,025.00

Local Currency Symbol

You can specify a new value for the L number format element, (the local currency symbol) either explicitly with the NLS_CURRENCY parameter or implicitly with the NLS_TERRITORY parameter.

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

Linguistic Sort Sequence

You can specify a new linguistic sort sequence or a binary sort either explicitly with the NLS_SORT parameter or implicitly with the NLS_LANGUAGE parameter.

Example VIII

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.

Changing the Optimization Approach and Mode

The Oracle optimizer can use either of these approaches to optimize a SQL statement:

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.
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.

 

With the cost-based approach, the optimizer can optimize a SQL statement with one of these goals:

best throughput	is the minimal time necessary to return all rows accessed by the statement.
best response time	is 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.

For information on how to choose a goal for the cost-based approach based on the characteristics of your application, see the Oracle8 Tuning.

FIPS Flagging

FIPS flagging causes an error message to be generated when a SQL statement is issued that is an extension of ANSI SQL92. 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 command will work, but generates the message, ORA-00097. This allows FIPS flagging to be altered without disconnecting the session.

Caching Session Cursors

If an application repeatedly issues parse calls on the same set of SQL statements, the reopening of the session cursors can affect performance. The ALTER SESSION SET SESSION_CACHED_CURSORS command allows frequently used session cursors to be stored in a session cache even if they are closed. This is particularly useful for some Oracle tools. For example, Oracle Forms applications close all session cursors associated with a form when switching to another form; in this case, frequently used cursors would not have to be reparsed.

Oracle uses the shared SQL area to determine whether more than three parse requests were issued on a given statement. If so, Oracle 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 cached automatically 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 Oracle8 Tuning.

Accessing the Database as if Connected to Another Instance in a Parallel Server

For optimum performance, each instance of a parallel server uses its own private rollback segments, freelist groups, and so on. A database is usually designed for a parallel server so that users connect to a particular instance and access data that is partitioned primarily for their use. If the users for that instance must connect to another instance, the data partitioning can be lost. The ALTER SESSION SET INSTANCE command allows users to access an instance as if they were connected to their usual instance.

Closing Database Links

A database link allows you to access a remote database in DELETE, INSERT, LOCK TABLE, SELECT, and UPDATE statements. When you issue a statement that uses a database link, Oracle creates a session for you on the remote database using the database 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.

You can use the CLOSE DATABASE LINK clause of the ALTER SESSION command to close a database link explicitly if you do not plan to use it again in your session. You may want to close a database link explicitly 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

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;

Forcing In-Doubt Distributed Transactions

If a network or machine failure occurs during the commit process for a distributed transaction, the state of the transaction may be unknown or in doubt. The transaction can be manually committed or rolled back on each database involved in the transaction with the FORCE clause of the COMMIT or ROLLBACK commands.

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 Oracle8 Distributed Database Systems.

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

This transaction inserts an employee record into the EMP table on the database identified by the database link SITE1 and deletes an employee record from the EMP table on the database identified by SITE2:

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.

Transaction Control in Procedures and Stored Functions

Procedures and stored functions are written in PL/SQL, and they can issue COMMIT and ROLLBACK statements. If your application performs record management that would be disrupted by a COMMIT or ROLLBACK statement not issued directly by the application itself, you may want to prevent procedures and stored functions called during your session from issuing these statements. You can do this with the following statement:

ALTER SESSION DISABLE COMMIT IN PROCEDURE;

If you subsequently call a procedure or a stored function that issues a COMMIT or ROLLBACK statement, Oracle returns an error and does not commit or roll back the transaction.

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.

Parallel DML

When parallel DML is enabled for your session, all DML portions of statements issued are considered for parallel execution. Even with parallel DML enabled, however, some DML operations are restricted from parallelization, while others may still execute serially unless parallel hints and clauses are specified. For a detailed description of parallel DML features and hints, see Oracle8 Tuning.

The following restrictions apply to parallel DML operations:

• DML operations on clustered tables are not parallelized.
• DML operations with embedded functions that either write or read database or package states are not parallelized.
• DML operations on tables with triggers that could fire are not parallelized.
• DML operations on tables or schema objects containing object types, LONGs, or LOB datatypes are not parallelized.

Parallel DML mode can be modified only between committed transactions. Issuing this command following an uncommitted transaction will generate an error. Uncommitted transactions must be either committed or rolled back prior to issuing the ALTER SESSION ENABLE|DISABLE|FORCE PARALLEL DML command.

Example I

Issue the following statement to enable parallel DML mode for the current session:

ALTER SESSION ENABLE PARALLEL DML;

Example II

The following example modifies the current session to check all deferrable constraints immediately following each DML statement:

ALTER SESSION SET CONSTRAINTS IMMEDIATE;

Example III

The following statement modifies the current session to allow inserts into local index partitions marked as unusable:

ALTER SESSION SET SKIP_UNUSABLE_INDEXES=TRUE;

Related Topics

"ALTER SESSION"

"SET CONSTRAINT(S)"

PL/SQL User's Guide and Reference

Oracle8 Reference

Oracle8 Tuning

ALTER SNAPSHOT

Purpose

To alter a snapshot in one of the following ways:

• changing its storage characteristics
• changing its automatic refresh mode and times

For illustrations of some of these purposes, see "Examples".

For more information on snapshots, including refreshing snapshots, see "CREATE SNAPSHOT".

Prerequisites

To alter a snapshot's storage parameters, the snapshot must be contained in your own schema, or you must have the ALTER ANY SNAPSHOT system privilege.

For detailed information about the prerequisites for ALTER SNAPSHOT, see Oracle8 Replication.

Syntax 

parallel_clause: See the "PARALLEL clause"

storage_clause: See the "STORAGE clause"

For the syntax of the following clauses, see "ALTER TABLE":

• physical_attributes_clause
• LOB_storage_clause
• modify_LOB_storage_clause
• modify_partition_clause
• move_partition_clause
• add_partition_clause
• split_partition_clause
• modify_default_attributes_clause
• rename_partition_clause

Keywords and Parameters

Examples

Example I

The following statement changes the automatic refresh mode for the HQ_EMP snapshot to FAST:

ALTER SNAPSHOT hq_emp
REFRESH FAST; 

The next automatic refresh of the snapshot will be a fast refresh provided it is a simple snapshot and its master table has a snapshot log that was created before the snapshot was created or last refreshed.

Because the REFRESH clause does not specify START WITH or NEXT values, the refresh intervals established by the REFRESH clause when the HQ_EMP snapshot was created or last altered are still used.

Example II

The following statement stores a new interval between automatic refreshes for the BRANCH_EMP snapshot:

ALTER SNAPSHOT branch_emp
REFRESH NEXT SYSDATE+7;

Because the REFRESH clause does not specify a START WITH value, the next automatic refresh occurs at the time established by the START WITH and NEXT values specified when the BRANCH_EMP snapshot was created or last altered.

At the time of the next automatic refresh, Oracle refreshes the snapshot, evaluates the NEXT expression SYSDATE+7 to determine the next automatic refresh time, and continues to refresh the snapshot automatically once a week.

Because the REFRESH clause does not explicitly specify a refresh mode, Oracle continues to use the refresh mode specified by the REFRESH clause of a previous CREATE SNAPSHOT or ALTER SNAPSHOT statement.

Example III

The following statement specifies a new refresh mode, next refresh time, and new interval between automatic refreshes of the SF_EMP snapshot:

ALTER SNAPSHOT sf_emp
REFRESH COMPLETE   
START WITH TRUNC(SYSDATE+1) + 9/24  
NEXT SYSDATE+7;

The START WITH value establishes the next automatic refresh for the snapshot to be 9:00 am tomorrow. At that point, Oracle performs a fast refresh of the snapshot, evaluates the NEXT expression, and subsequently refreshes the snapshot every week.

Specifying Rollback Segments

You can specify the rollback segments to be used during a refresh for both the master site and the local site. The master rollback segment is stored on a per-snapshot basis and is validated during snapshot creation and refresh. If the snapshot is complex, the master rollback segment, if specified, is ignored.

You can change local snapshot rollback segments using the DBMS_REFRESH package and is stored at the refresh group level. For information about the DBMS_REFRESH package, see Oracle8 Replication. If the auto-refresh parameters (START WITH and NEXT) are specified, a new refresh group is automatically created to refresh the snapshot with a background process. The local rollback segment, if specified, is associated with this new refresh group. An error is raised if the auto-refresh parameters are not specified, but a local rollback segment is.

Example I

The following example changes the remote master rollback segment used during snapshot refresh to MASTER_SEG:

ALTER SNAPSHOT inventory 
  REFRESH USING MASTER ROLLBACK SEGMENT master_seg;

Example II

The following example changes the remote master rollback segment used during snapshot refresh to one chosen by Oracle:

ALTER SNAPSHOT sales REFRESH USING DEFAULT MASTER ROLLBACK SEGMENT;

Primary Key Snapshots

To change a ROWID snapshot to a primary key snapshot you must:

• include all columns of the primary key in the snapshot definition
• have an enabled primary key constraint defined on the snapshot master
• ensure that the updatable snapshot log is empty for all updatable snapshots

To fast refresh primary key snapshots you must first create a snapshot master log specifying WITH PRIMARY KEY. The snapshot master log can also store ROWIDs. The snapshot master log must be created before the snapshot is created in order for the snapshots to use the log to fast refresh.

For detailed information about primary key snapshots, see Oracle8 Reference

Example I

The following example changes a ROWID to a primary key snapshot:

ALTER SNAPSHOT emp_rs REFRESH WITH PRIMARY KEY;

Partitioned Snapshots

Partitioned snapshots are the same as partitioned tables because snapshots are basically tables. The options have the same syntax and semantics as the partitioned table options for CREATE TABLE and ALTER TABLE. The only difference is that the following operations are not allowed on snapshots and snapshot logs:

• DROP PARTITION
• TRUNCATE PARTITION
• EXCHANGE PARTITION

You cannot perform bulk deletions by dropping or truncating partitions on master tables. Thus, after dropping or truncating a partition, all snapshots must be refreshed manually. A fast refresh will probably produce incorrect results, but Oracle will not raise an error.

Related Topics

"CREATE SNAPSHOT"

"DROP SNAPSHOT"

"STORAGE clause"

ALTER SNAPSHOT LOG

Purpose

Changes the storage characteristics of a snapshot log. For more information on snapshot logs, see "CREATE SNAPSHOT".

Prerequisites

Only the owner of the master table or a user with the SELECT privilege for the master table can alter a snapshot log. For detailed information about the prerequisites for ALTER SNAPSHOT LOG, see Oracle8 Replication.

Syntax

For the syntax of the following clauses, see "ALTER TABLE":

• physical_attributes_clause
• modify_partition_clause
• rename_partition_clause
• move_partition_clause
• add_partition_clause
• split_partition_clause
• modify_default_attributes

Keywords and Parameters

Modifying Physical Attributes

Example

The following statement changes the MAXEXTENTS value of a snapshot log:

ALTER SNAPSHOT LOG ON dept  
STORAGE MAXEXTENTS 50;

Adding Primary Key, ROWID, and Filter Columns

Snapshot logs can be altered to additionally record primary key, ROWID, or filter column information when snapshot master tables are updated. To stop recording any of this information, you must first drop the snapshot log and then re-create it.

Example

The following example alters an existing ROWID snapshot log to also record primary key information:

ALTER SNAPSHOT LOG ON sales ADD PRIMARY KEY;

Partitioned Snapshot Logs

Partitioned snapshot logs are the same as partitioned tables, because snapshot logs are basically tables. The options have the same syntax and semantics as the partitioned table options for CREATE TABLE and ALTER TABLE. The only difference is that the following operations are not allowed on snapshots and snapshot logs:

• DROP PARTITION
• TRUNCATE PARTITION
• EXCHANGE PARTITION

You cannot perform bulk deletions by dropping or truncating partitions on master tables. Therefore, after dropping or truncating a partition, all snapshots must be manually refreshed. A fast refresh will probably produce incorrect results, but Oracle will not raise an error.

Related Topics

"ALTER TABLE"

"CREATE SNAPSHOT"

"CREATE SNAPSHOT LOG"

"DROP SNAPSHOT LOG"

ALTER SYSTEM

Purpose

To dynamically alter your Oracle instance in one of the following ways:

• to explicitly perform a checkpoint
• to verify access to datafiles
• to terminate a session
• to enable or disable resource limits
• to enable distributed recovery in a single-process environment
• to disable distributed recovery
• to restrict logons to Oracle to only those users with RESTRICTED SESSION system privilege
• to clear all data from the shared pool in the system global area (SGA)
• to enable or disable global name resolution
• to dynamically change or disable limits or thresholds for concurrent usage licensing and named user licensing
• to manually archive redo log file groups or to enable or disable automatic archiving
• to manage shared server processes or dispatcher processes for the multithreaded server architecture
• to explicitly switch redo log file groups

Prerequisites

You must have ALTER SYSTEM system privilege.

Syntax

archive_log_clause: See the "ARCHIVE LOG clause".

set_clause::=

dispatch_clause::=
 

options_clause::=

Keywords and Parameters

Restricting Logons

By default, any user granted CREATE SESSION system privilege can log on to Oracle. The ENABLE RESTRICTED SESSION option of the ALTER SYSTEM command prevents logons by all users except those having RESTRICTED SESSION system privilege. Existing sessions are not terminated.

You may want to restrict logons if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on. To restrict logons, issue the following statement:

ALTER SYSTEM
ENABLE RESTRICTED SESSION;

You can then terminate any existing sessions using the KILL SESSION clause of the ALTER SYSTEM command.

After performing maintenance on your application, issue the following statement to allow any user with CREATE SESSION system privilege to log on:

ALTER SYSTEM
DISABLE RESTRICTED SESSION;

Clearing the Shared Pool

The FLUSH SHARED_POOL option of the ALTER SYSTEM command clears all information from the shared pool in the system global area (SGA). The shared pool stores this information:

• cached data dictionary information
• shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages, and triggers

You might want to clear the shared pool before beginning performance analysis. To clear the shared pool, issue the following statement:

ALTER SYSTEM
FLUSH SHARED_POOL; 

The above statement does not clear shared SQL and PL/SQL areas for SQL statements, stored procedures, functions, packages, or triggers that are currently being executed, or for SQL SELECT statements for which all rows have not yet been fetched.

Performing a Checkpoint

The CHECKPOINT clause of the ALTER SYSTEM command explicitly forces Oracle to perform a checkpoint. You can force a checkpoint if you want to ensure that all changes made by committed transactions are written to the data files on disk. For more information on checkpoints, see the "Recovery Structures" chapter of Oracle8 Concepts.

If you are using Oracle with the Parallel Server option in parallel mode, you can specify either the GLOBAL option to perform a checkpoint on all instances that have opened the database or the LOCAL option to perform a checkpoint on only your instance.

The following statement forces a checkpoint:

ALTER SYSTEM 
CHECKPOINT; 

Oracle does not return control to you until the checkpoint is complete.

Checking Datafiles

The CHECK DATAFILES clause of the ALTER SYSTEM command verifies access to all online datafiles. If any datafile is not accessible, Oracle writes a message to an ALERT file. You may want to perform this operation after fixing a hardware problem that prevented an instance from accessing a datafile. For more information on using this clause, see Oracle8 Parallel Server Concepts and Administration.

The following statement verifies that all instances that have opened the database can access all online datafiles:

ALTER SYSTEM
CHECK DATAFILES GLOBAL;

Using Resource Limits

When you start an instance, Oracle enables or disables resource limits based on the value of the initialization parameter RESOURCE_LIMIT. You can issue an ALTER SYSTEM statement with the RESOURCE_LIMIT option to enable or disable resource limits for subsequent sessions.

Enabling resource limits only causes Oracle to enforce the resource limits already assigned to users. To choose resource limit values for a user, you must create a profile, or a set of limits, and assign that profile to the user. For more information on this process, see "CREATE PROFILE" and "CREATE USER".

This ALTER SYSTEM statement dynamically enables resource limits:

ALTER SYSTEM
SET RESOURCE_LIMIT = TRUE;

Global Name Resolution

When you start an instance, Oracle determines whether to enforce global name resolution for remote objects accessed in SQL statements based on the value of the initialization parameter GLOBAL_NAMES. You can subsequently enable or disable global name resolution while your instance is running with the GLOBAL_NAMES parameter of the ALTER SYSTEM command. You can also enable or disable global name resolution for your session with the GLOBAL_NAMES parameter of the ALTER SESSION command discussed earlier in this chapter.

Oracle recommends that you enable global name resolution if you use or plan to use distributed processing. For more information on global name resolution and how Oracle enforces it, see "Referring to Objects in Remote Databases" and Oracle8 Distributed Database Systems.

Managing Processes for the Multithreaded Server

When you start your instance, Oracle creates shared server processes and dispatcher processes for the multithreaded server architecture based on the values of the following initialization parameters:

MTS_SERVERS	specifies the initial and minimum number of shared server processes. Oracle may automatically change the number of shared server processes if the load on the existing processes changes. While your instance is running, the number of shared server processes can vary between the values of the initialization parameters MTS_SERVERS and MTS_MAX_SERVERS.
MTS_DISPATCHERS	specifies one or more network protocols and the number of dispatcher processes for each protocol.

 

For more information on the multithreaded server architecture, see Oracle8 Concepts.

You can use the MTS_SERVERS and MTS_DISPATCHERS parameters of the ALTER SYSTEM command to perform one of the following operations while the instance is running:

• To create additional shared server processes: You can cause Oracle to create additional shared server processes by increasing the minimum number of shared server processes.
• To terminate existing shared server processes: Oracle terminates the shared server processes after it finishes processing their current calls, unless the load on the server processes is so high that it cannot be managed by the remaining processes.
• To create more dispatcher processes for a specific protocol: You can create additional dispatcher processes up to a maximum across all protocols specified by the initialization parameter MTS_MAX_DISPATCHERS.

You cannot use this command to create dispatcher processes for network protocols that are not specified by the initialization parameter MTS_DISPATCHERS. To create dispatcher processes for a new protocol, you must change the value of the initialization parameter.

• To terminate existing dispatcher processes for a specific protocol: Oracle terminates the dispatcher processes only after their current user processes disconnect from the instance.

Example I

The following statement changes the minimum number of shared server processes to 25:

ALTER SYSTEM
SET MTS_SERVERS = 25; 

If there are currently fewer than 25 shared server processes, Oracle creates more. If there are currently more than 25, Oracle terminates some of them when they are finished processing their current calls if the load could be managed by the remaining 25.

Example II

The following statement dynamically changes the number of dispatcher processes for the TCP/IP protocol to 5 and the number of dispatcher processes for the DECNET protocol to 10:

ALTER SYSTEM 
SET MTS_DISPATCHERS = 'TCP, 5'
MTS_DISPATCHERS = 'DECnet, 10'; 

If there are currently fewer than 5 dispatcher processes for TCP, Oracle creates new ones. If there are currently more than 5, Oracle terminates some of them after the connected users disconnect.

If there are currently fewer than 10 dispatcher processes for DECnet, Oracle creates new ones. If there are currently more than 10, Oracle terminates some of them after the connected users disconnect.

If there are currently existing dispatchers for another protocol, the above statement does not affect the number of dispatchers for that protocol.

Using Licensing Limits

Oracle enforces concurrent usage licensing and named user licensing limits specified by your Oracle license. When you start your instance, Oracle establishes the licensing limits based on the values of the following initialization parameters:

LICENSE_MAX_SESSIONS	establishes the concurrent usage licensing limit, or the limit for concurrent sessions. Once this limit is reached, only users with RESTRICTED SESSION system privilege can connect.
LICENSE_SESSIONS_WARNING	establishes a warning threshold for concurrent usage. Once this threshold is reached, Oracle writes warning messages to the database ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.
LICENSE_MAX_USERS	establishes the limit for users connected to your database. Once this limit is reached, more users cannot connect.

 

You can dynamically change or disable limits or thresholds while your instance is running using the LICENSE_MAX_SESSIONS, LICENSE_SESSIONS_WARNING, and LICENSE_MAX_USERS parameters of the ALTER SYSTEM command. Do not disable or raise session or user limits unless you have appropriately upgraded your Oracle license. For information on upgrading your license, contact your Oracle sales representative.

New limits apply only to future sessions and users:

• If you reduce the limit on sessions below the current number of sessions, Oracle does not end existing sessions to enforce the new limit. Users without RESTRICTED SESSION system privilege can begin new sessions only when the number of sessions falls below the new limit.
• If you reduce the warning threshold for sessions below the current number of sessions, Oracle writes a message to the ALERT file for all subsequent sessions.
• You cannot reduce the limit on users below the current number of users created for the database.

Example I

The following statement dynamically changes the limit on sessions for your instance to 64 and the warning threshold for sessions on your instance to 54:

ALTER SYSTEM 
SET LICENSE_MAX_SESSIONS = 64 
LICENSE_SESSIONS_WARNING = 54; 

If the number of sessions reaches 54, Oracle writes a warning message to the ALERT file for each subsequent session. Also, users with RESTRICTED SESSION system privilege receive warning messages when they begin subsequent sessions.

If the number of sessions reaches 64, only users with RESTRICTED SESSION system privilege can begin new sessions until the number of sessions falls below 64 again.

Example II

The following statement dynamically disables the limit for sessions on your instance:

ALTER SYSTEM
SET LICENSE_MAX_SESSIONS = 0; 

After you issue the above statement, Oracle no longer limits the number of sessions on your instance.

Example III

The following statement dynamically changes the limit on the number of users in the database to 200:

ALTER SYSTEM
SET LICENSE_MAX_USERS = 200; 

After you issue the above statement, Oracle prevents the number of users in the database from exceeding 200.

Switching Redo Log File Groups

The SWITCH LOGFILE option of the ALTER SYSTEM command explicitly forces Oracle to begin writing to a new redo log file group, regardless of whether the files in the current redo log file group are full. You may want to force a log switch to drop or rename the current redo log file group or one of its members, because you cannot drop or rename a file while Oracle is writing to it. The forced log switch affects only your instance's redo log thread. Note that when you force a log switch, Oracle begins to perform a checkpoint. Oracle returns control to you immediately rather than when the associated checkpoint is complete.

The following statement forces a log switch:

ALTER SYSTEM
SWITCH LOGFILE;

Enabling and Disabling Distributed Recovery

Oracle allows you to perform distributed transactions, or transactions that modify data on multiple databases. If a network or machine failure occurs during the commit process for a distributed transaction, the state of the transaction may be unknown, or in doubt. Once the failure has been corrected and the network and its nodes are back online, Oracle recovers the transaction.

If you are using Oracle in multiple-process mode, this distributed recovery is performed automatically. If you are using Oracle in single-process (single user) mode, such as on the MS-DOS operating system, you must explicitly initiate distributed recovery with the following statement.

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

You may need to issue the above statement more than once to recover an in-doubt transaction, especially if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view DBA_2PC_PENDING. You can tell that the transaction is recovered when it no longer appears in DBA_2PC_PENDING. For more information about distributed transactions and distributed recovery, see Oracle8 Distributed Database Systems.

You can disable distributed recovery in both single-process and multiprocess mode with the following statement:

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY; 

You may want to disable distributed recovery for demonstration purposes. You can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.

Terminating a Session

The KILL SESSION clause of the ALTER SYSTEM command terminates a session, immediately performing the following tasks:

• rolling back its current transactions
• releasing all of its locks
• freeing all of its resources

You may want to kill the session of a user that is holding resources needed by other users. The user receives an error message indicating that the session has been killed; that user can no longer make calls to the database without beginning a new session. You can kill a session only on the same instance as your current session.

If you try to kill a session that is performing some activity that must be completed, such as waiting for a reply from a remote database or rolling back a transaction, Oracle waits for this activity to complete, kills the session, and then returns control to you. If the waiting lasts as long as a minute, Oracle marks the session to be killed and returns control to you with a message indicating that the session is marked to be killed. Oracle then kills the session when the activity is complete.

Example

Consider this data from the V$SESSION dynamic performance table:

SELECT sid, serial, username
FROM v$session 

  SID    SERIAL USERNAME
----- --------- ----------------
    1         1
    2         1
    3         1
    4         1 
    5         1 
    7         1 
    8        28 OPS$BQUIGLEY 
   10       211 OPS$SWIFT 
   11        39 OPS$OBRIEN 
   12        13 SYSTEM  
   13         8 SCOTT 

The following statement kills the session of the user SCOTT using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM
KILL SESSION '13, 8';

Disconnecting a Session

The DISCONNECT SESSION clause is similar to the KILL SESSION clause, but with two distinct differences.

First, the ALTER SYSTEM DISCONNECT SESSION 'X, Y' POST_TRANSACTION command waits until any current transaction that the session is working on completes before taking effect.

Second, the session is disconnected rather than killed, which means that the dedicated server process (or virtual circuit if the connection was made through MTS) is destroyed by this command. Termination of a session's connection causes application failover to take effect if the appropriate system parameters are configured.

Disconnecting a session essentially allows you to perform a manual application failover. Using this command in a parallel server environment allows you to disconnect sessions on an overloaded instance and shift them to another instance.

The POST_TRANSACTION keyword is required.

Example

The following statement disconnects user SCOTT's session, using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM
DISCONNECT SESSION '13, 8' POST_TRANSACTION;

For more information about application failover, see Oracle8 Parallel Server Concepts and Administration and Oracle8 Tuning.

Related Topics

"ALTER SESSION"

"CREATE USER"

"ARCHIVE LOG clause"

---

Prev   Next

Copyright © 1997 Oracle Corporation.  All Rights Reserved.

Library   Product   Contents   Index