Oracle8 SQL Reference 
Release 8.0 
A58225-01
 
Library
 
Product
 
Contents
 
Index
 

Prev Next


ALTER SESSION

Purpose

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

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

ADVISE 

sends advice to a remote database to force a distributed transaction. This advice appears on the remote database in the ADVICE column of the DBA_2PC_PENDING data dictionary view in the event of an in-doubt distributed transaction. (See also "Forcing In-Doubt Distributed Transactions".)The following are advice options: 

 

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. 

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. For more information, see "Closing Database Links"

COMMIT IN PROCEDURE 

ENABLE 

permits procedures and stored functions to issue these statements. 

 

DISABLE 

prohibits procedures and stored functions from issuing these statements. 

 

See also "Transaction Control in Procedures and Stored Functions"

PARALLEL DML 

specifies whether all subsequent DML transactions in the session will be considered for parallel execution. (See also "Parallel DML".) 

 

You can execute this option only between committed transactions. Uncommitted transactions must either be committed or rolled back prior to executing this command. 

 

ENABLE 

executes the session's DML statements in parallel mode if a parallel hint or a parallel clause is specified. 

 

DISABLE 

executes the session's DML statements serially. This is the default mode. 

 

FORCE 

forces parallel execution of subsequent DML statements in the session if none of the parallel DML restrictions are violated. If no parallel clause or hint is specified, then a default level of parallelism (for both degree and instances) is used.  

Note: Using FORCE 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 and default instances) with the CREATE TABLE statement. 

SET 

sets the session parameters that follow. 

CLOSE_OPEN_CACHED_CURSORS 

controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT or ROLLBACK. 

 

TRUE 

causes open cursors to be closed at each COMMIT or ROLLBACK. 

 

FALSE 

signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. 

CONSTRAINT[S] 

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; equivalent to issuing the SET CONSTRAINTS ALL IMMEDIATE command at the beginning of each transaction in your session. See the IMMEDIATE parameter of "SET CONSTRAINT(S)"

 

DEFERRED 

indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed; equivalent to issuing the SET CONSTRAINTS ALL DEFERRED command at the beginning of each transaction in your session. See the DEFERRED parameter of "SET CONSTRAINT(S)"

 

DEFAULT 

restores all constraints at the beginning of each transaction to their initial state of DEFERRED or IMMEDIATE. 

FLAGGER 

specifies FIPS flagging. See also "FIPS Flagging"

 

ENTRY 

flags for SQL92 Entry level. 

 

INTERMEDIATE 

flags for SQL92 Intermediate level. 

 

FULL 

flags for SQL92 Full level. 

 

OFF 

turns off flagging 

GLOBAL_NAMES 

controls the enforcement of global name resolution for your session. For information on enabling and disabling global name resolution with this parameter, see "ALTER SYSTEM"

 

TRUE 

enables global name resolution. 

 

FALSE 

disables global name resolution. 

HASH_JOIN_ENABLED 

enables or disables the use of the hash join operation in queries. The default is TRUE, which enables 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 K. The default value for this parameter is 1. If the multithreaded server is used, the value is always 1, and any value given here is ignored. 

INSTANCE 

in a parallel server, accesses database files as if the session were connected to the instance specified by integer. For more information, see "Accessing the Database as if Connected to Another Instance in a Parallel Server"

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 requires row locks held by another transaction, then the DML statement will wait until the row locks are released. 

MAX_DUMP_FILE_SIZE 

specifies the upper limit of trace dump file size. Specify the maximum size as either a nonnegative integer that represents the number of blocks, or as 'UNLIMITED'. If 'UNLIMITED' is specified, no upper limit is imposed. 

For more information on the following NLS parameters, see "Using NLS Parameters"

NLS_LANGUAGE 

changes the language in which Oracle returns errors and other messages. This parameter also implicitly specifies new values for these items: 

 

  • language for day and month names and abbreviations and spelled values of other elements 
  • sort sequences 
  • B.C. and A.D. indicators 
  • A.M. and P.M. meridian indicators 
 

NLS_TERRITORY 

implicitly specifies new values for these items: 

 

  • default date format 
  • decimal character and group separators 
  • local currency symbol 
  • ISO currency symbol 
  • first day of the week for D date format element 
 

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 Models"

NLS_DATE_LANGUAGE 

explicitly changes the language for day and month names and abbreviations and spelled values of other date format elements. 

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, and 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, or ">" 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 Oracle 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. 

OPTIMIZER_MODE 

specifies the approach and mode of the optimizer for your session. For more information on optimizer mode, see "Changing the Optimization Approach and Mode"

 

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. 

 

RULE 

specifies the rule-based approach. 

 

CHOOSE 

causes the optimizer to choose an optimization approach based on the presence of statistics in the data dictionary. 

PARTITION_VIEW_ENABLED 

When set to TRUE, this parameter causes the optimizer to skip unnecessary table accesses in a partition view. For more information, see Oracle8 Reference. 

PLSQL_V2_COMPATABILITY 

modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). See the PL/SQL User's Guide and Reference and Oracle8 Reference. for more information about this session parameter. 

 

TRUE 

enables Oracle8 PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs. 

 

FALSE 

disallows illegal Oracle7 PL/SQL V2 constructs. This is the default. 

REMOTE_DEPENDENCIES_MODE 

specifies how dependencies of remote stored procedures are handled by the session. For more information, refer Oracle8 Application Developer's Guide. 

SESSION_CACHED_CURSORS 

specifies the size of the session cache for holding frequently used cursors. integer specifies how many cursors can be retained in the cache. For more information on this parameter, see "Caching Session Cursors"

SKIP_UNUSABLE_INDEXES 

 

 

controls the use and reporting of tables with unusable indexes or index partitions. 

 

TRUE 

disables error reporting of indexes marked as unusable. Allows inserts, deletes, and updates to tables with unusable indexes or index partitions. 

 

FALSE 

enables error reporting of indexes marked as unusable. Does not allow inserts, deletes, and updates to tables with unusable indexes or index partitions. This is the default. 

SQL_TRACE 

controls the SQL trace facility for your session. See also "Enabling and Disabling the SQL Trace Facility"

 

TRUE 

enables the SQL trace facility. 

 

FALSE 

disables the SQL trace facility. 

 

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

Note:  

The language you select must already have been installed. Refer to your operating system specific installation instructions. 


 
 

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.


Note:  

Some applications (such as SQL*Forms) automatically prohibit COMMIT and ROLLBACK statements in procedures and stored functions. Refer to your application documentation. 


 
 

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:

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

ALTER SNAPSHOT

Purpose

To alter a snapshot in one of the following ways:

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":

Keywords and Parameters

schema 

is the schema containing the snapshot. If you omit schema, Oracle assumes the snapshot is in your own schema. 

snapshot 

is the name of the snapshot to be altered. 

modify_default_attributes 

specifies new values for the default attributes of a partitioned table. For information about specifying the parameters of this clause, see "ALTER TABLE"

physical_attributes_clause 

change the values of the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters and the storage characteristics for the internal table that Oracle uses to maintain the snapshot's data. For more information, see "CREATE TABLE" and the "STORAGE clause"

LOGGING/NOLOGGING 

specifies the logging attribute. For information about specifying this option, see "ALTER TABLE"

CACHE/NOCACHE 

for data that will be accessed frequently, specifies whether the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. For information about specifying this option, see "ALTER TABLE"

LOB_storage_clause 

specifies the LOB storage characteristics. For information about specifying the parameters of this clause, see "ALTER TABLE"

modify_LOB_storage_clause 

modifies the physical attributes of the LOB attribute lob_item or LOB object attribute. For information about specifying the parameters of this clause, see "ALTER TABLE"

For more information on the following partitioning clauses, see "Partitioned Snapshots"

modify_partition_clause 

modifies the real physical attributes of a table partition. For information about specifying the parameters of this clause, see "ALTER TABLE"

move_partition_clause 

moves table partition partition_name to another segment. For information about specifying the parameters of this clause, see "ALTER TABLE"

add_partition_clause 

adds a new partition new_partition_name to the "high" end of a partitioned table. For information about specifying the parameters of this clause, see "ALTER TABLE"

split_partition_clause 

creates two new partitions, each with a new segment and new physical attributes, and new initial extents. For information about specifying the parameters of this clause, see "ALTER TABLE"

rename_partition_clause 

renames table partition partition_name to new_partition_name. For information about specifying the parameters of this clause, see "ALTER TABLE"

parallel_clause 

specifies the degree of parallelism for the snapshot. See the PARALLEL clause on page 4-564. When this clause is set for master tables, performance for snapshot creation and refresh may improve (depending on the snapshot definition query). 

MODIFY PARTITION UNUSABLE LOCAL INDEXES 

 

marks all the local index partitions associated with partition_name as unusable. 

MODIFY PARTITION REBUILD UNUSABLE LOCAL INDEXES 

 

rebuilds the unusable local index partitions associated with partition_name

USING INDEX 

changes the value of INITRANS, MAXTRANS, and STORAGE parameters for the index Oracle uses to maintain the snapshot's data. If USING INDEX is not specified then default values are used for the index. 

REFRESH 

changes the mode and times for automatic refreshes. 

 

FAST 

specifies a fast refresh, or a refresh using the snapshot log associated with the master table. 

 

COMPLETE 

specifies a complete refresh, or a refresh that re-creates the snapshot during each refresh. 

 

FORCE 

specifies a fast refresh if one is possible or complete refresh if a fast refresh is not possible. Oracle decides whether a fast refresh is possible at refresh time. 

 

If you omit the FAST, COMPLETE, and FORCE options, Oracle uses FORCE by default. 

 

START WITH 

specifies a date expression for the next automatic refresh time. 

 

NEXT 

specifies a new date expression for calculating the interval between automatic refreshes. 

 

START WITH and NEXT values must evaluate to times in the future. 

 

WITH PRIMARY KEY 

changes a ROWID snapshot to a primary key snapshot. Primary key snapshots allow snapshot master tables to be reorganized without impacting the snapshot's ability to continue to fast refresh. The master table must contain an enabled primary key constraint. See also "Primary Key Snapshots"

USING MASTER ROLLBACK SEGMENT 

changes remote master rollback segment to be used during snapshot refresh; rollback_segment is the name of the rollback segment to be used. (To change the local snapshot rollback segment, use the DBMS_REFRESH package in Oracle8 Reference.) See also "Specifying Rollback Segments"

 

DEFAULT 

specifies that Oracle will choose which rollback segment to use. If you specify DEFAULT, you cannot specify rollback_segment. 

 

 

MASTER 

specifies the remote rollback segment to be used at the remote master for the individual snapshot. 

 

LOCAL 

specifies the remote rollback segment to be used for the local refresh group that contains the snapshot. 

 

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.


Note:  

To direct Oracle to select the rollback segment automatically after one has been specified using CREATE SNAPSHOT or ALTER SNAPSHOT, specify the DEFAULT option with ALTER SNAPSHOT. 


 
 
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:

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


Note:  

Primary key snapshots cannot be altered to ROWID snapshots. You must drop the primary key snapshot and re-create it as a ROWID snapshot. 


 
 
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:

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":

Keywords and Parameters

schema 

is the schema containing the master table. If you omit schema, Oracle assumes the snapshot log is in your own schema. 

table 

is the name of the master table associated with the snapshot log to be altered. 

physical_attributes_clause 

changes the value of PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters for the table, partition, the overflow data segment, or the default characteristics of a partitioned table. See the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters of "CREATE TABLE". See the example under "Modifying Physical Attributes"

rename_partition_clause 

renames table partition partition_name to new_partition_name. For information about specifying the parameters of this clause, see "ALTER TABLE"

modify_partition_clause 

modifies the real physical attributes of a table partition. For information about specifying the parameters of this clause, see "ALTER TABLE"

move_partition_clause 

moves table partition partition_name to another segment. For information about specifying the parameters of this clause, see "ALTER TABLE"

add_partition_clause 

adds a new partition new_partition_name to the "high" end of a partitioned table. For information about specifying the parameters of this clause, see "ALTER TABLE"

split_partition_clause 

creates two new partitions, each with a new segment and new physical attributes, and new initial extents. For information about specifying the parameters of this clause, see "ALTER TABLE" 

For more information see "Partitioned Snapshot Logs"

modify_default_attributes_clause 

is a valid option only for a partitioned index. Use this option to specify new values for the default attributes of a partitioned index. 

parallel_clause 

specifies the degree of parallelism for the snapshot. See the PARALLEL clause. When this clause is set for master tables, performance during snapshot creation and refresh may improve (depending on the snapshot definition query). 

LOGGING/NOLOGGING 

specifies the logging attribute. For information about specifying this option, see "ALTER TABLE"

CACHE/NOCACHE 

for data that will be accessed frequently, specifies whether the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. For information about specifying this option, see "ALTER TABLE"

ADD 

changes the snapshot log so that it records the primary key values or ROWID values when rows in the snapshot master table are updated. This clause can also be used to record additional filter columns. 

 

PRIMARY KEY 

specifies that the primary-key values of all rows updated should be recorded in the snapshot log. 

 

ROWID 

specifies that the ROWID values of all rows updated should be recorded in the snapshot log. 

 

filter_column(s) 

are non-primary-key columns referenced by snapshots. For information about filter columns, see Oracle8 Replication

 

For more information, see "Adding Primary Key, ROWID, and Filter Columns"

 

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:

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:

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

You can use the following options regardless of whether your instance has the database dismounted or mounted, open or closed: 

RESTRICTED SESSION 

specifies whether logon to Oracle is restricted 

 

ENABLE 

allows only users with RESTRICTED SESSION system privilege to logon to Oracle. 

 

DISABLE 

reverses the effect of the ENABLE RESTRICTED SESSION option, allowing all users with CREATE SESSION system privilege to log on to Oracle. 

 

For more information, see "Restricting Logons"

FLUSH SHARED_POOL 

clears all data from the shared pool in the system global area (SGA). For more information, see "Clearing the Shared Pool"

You can use the following options when your instance has the database mounted, open or closed: 

CHECKPOINT 

performs a checkpoint. 

 

GLOBAL 

performs a checkpoint for all instances that have opened the database. 

 

LOCAL 

performs a checkpoint only for the thread of redo log file groups for your instance. You can use this option only when your instance has the database open. 

 

If you omit both the GLOBAL and LOCAL options, Oracle performs a global checkpoint. For more information, see "Performing a Checkpoint"

CHECK DATAFILES 

GLOBAL 

verifies that all instances that have opened the database can access all online datafiles. 

 

LOCAL 

verifies that your instance can access all online datafiles. 

 

If you omit both the GLOBAL and LOCAL options, Oracle uses GLOBAL by default. For more information, see "Checking Datafiles"

You can use the following parameters and options only when your instance has the database open: 

RESOURCE_LIMIT 

controls resource limits. TRUE enables resource limits; FALSE disables resource limits. See also "Using Resource Limits"

GLOBAL_NAMES 

controls the enforcement of global name resolution for your session. TRUE enables the enforcement of global names; FALSE disables the enforcement of global names. For more information, see "Global Name Resolution"

SCAN_INSTANCES 

in a parallel server, specifies the number of instances to participate in parallelized operations. This syntax will be obsolete in the next major release. 

CACHE_INSTANCES 

in a parallel server, specifies the number of instances that will cache a table. This syntax will be obsolete in the next major release. 

For more information on parallel operations, see Oracle8 Tuning 

For more information on the following multithreaded server parameters, see "Managing Processes for the Multithreaded Server"

MTS_SERVERS 

specifies a new minimum number of shared server processes. 

MTS_DISPATCHERS 

specifies a new number of dispatcher processes: 

 

protocol 

is the network protocol of the dispatcher processes. 

 

integer 

is the new number of dispatcher processes of the specified protocol. 

 

You can specify multiple MTS_DISPATCHERS parameters in a single command for multiple network protocols. 

For more information on the following licensing parameters, see "Using Licensing Limits"

JOB_QUEUE_PROCESSES 

specifies the number of job queue processes per instance (SNPn, where n is 0 to 9 followed by A to Z). Set this parameter to 1 or higher if you wish to have your snapshots updated automatically. One job queue process is usually sufficient unless you have many snapshots that refresh simultaneously.  

Oracle also uses job queue processes to process requests created by the DBMS_JOB package. For more information on managing table snapshots, see Oracle8 Replication. 

LICENSE_MAX_SESSIONS 

limits the number OS sessions on your instance. A value of 0 disables the limit. 

LICENSE_SESSIONS_WARNING 

establishes a threshold of sessions over which Oracle writes warning messages to the ALERT file for subsequent sessions. A value of 0 disables the warning threshold. 

LICENSE_MAX_USERS 

limits number of concurrent users on your database. A value of 0 disables the limit. 

REMOTE_DEPENDENCIES_MODE 

specifies how dependencies of remote stored procedures are handled by the server. For more information, refer to Oracle8 Application Developer's Guide 

 

SWITCH LOGFILE 

switches redo log file groups. For more information, see "Switching Redo Log File Groups"

DISTRIBUTED RECOVERY 

specifies whether or not distributed recovery is enabled. 

 

ENABLE 

enables distributed recovery. In a single-process environment, you must use this option to initiate distributed recovery. 

 

DISABLE 

switches redo log files. 

 

For more information, see "Enabling and Disabling Distributed Recovery"

ARCHIVE LOG 

manually archives redo log files or enables or disables automatic archiving. See the "ARCHIVE LOG clause"

KILL SESSION 

terminates a session and any ongoing transactions. You must identify the session with both of the following values from the V$SESSION view: 

 

integer1 

is the value of the SID column. 

 

integer2 

is the value of the SERIAL# column. 

 

For more information, see "Terminating a Session"

DISCONNECT SESSION 

disconnects the current session by destroying the dedicated server process (or virtual circuit if the connection was made via MTS). If configured, application failover will take effect. For more information about application failover see Oracle8 Tuning and Oracle8 Parallel Server Concepts and Administration. You must identify the session with both of the following values from the V$SESSION view: 

 

integer1 

is the value of the SID column. 

 

integer2 

is the value of the SERIAL# column. 

 

POST_TRANSACTION 

allows ongoing transactions to complete before the session is disconnected. This keyword is required when DISCONNECT SESSION is specified. For more information, see "Disconnecting a Session"

PLSQL_V2_COMPATIBILITY 

modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). See the PL/SQL User's Guide and Reference and Oracle8 Reference for more information about this system parameter. 

 

TRUE 

enables Oracle8 PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs. 

 

FALSE 

disallows illegal Oracle7 PL/SQL V2 constructs. This is the default. 

MAX_DUMP_FILE_SIZE 

specifies the trace dump file size upper limit for all user sessions. Specify the maximum size as either a nonnegative integer that represents the number of blocks, or as 'UNLIMITED'. If you specify 'UNLIMITED', no upper limit is imposed. 

 

DEFERRED 

modifies the trace dump file size upper limit for future user sessions only. 

 

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:

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:

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:

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:

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 
Prev
Next 
Next
 
Oracle 
Copyright © 1997 Oracle Corporation. 
All Rights Reserved. 
 
Library
 
Product
 
Contents
 
Index