SQL Statements (continued), 5 of 5

ALTER SYSTEM

Syntax

end_session_clauses::=

archive_log_clause::=

set_clause::=

Purpose

To dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted.

Prerequisites

You must have ALTER SYSTEM system privilege.

To specify the archive_log_clause, you must have the OSDBA or OSOPER role enabled.

Keywords and Parameters

Examples

Archive Log Examples

The following statement manually archives the redo log file group with the log sequence number 4 in thread number 3:

ALTER SYSTEM ARCHIVE LOG THREAD 3 SEQUENCE 4;

The following statement manually archives the redo log file group containing the redo log entry with the SCN 9356083:

ALTER SYSTEM ARCHIVE LOG CHANGE 9356083;

The following statement manually archives the redo log file group containing a member named 'DISKL:LOG6.LOG' to an archived redo log file in the location 'DISKA:[ARCH$]':

ALTER SYSTEM ARCHIVE LOG 
    LOGFILE 'diskl:log6.log' 
    TO 'diska:[arch$]';

Query Rewrite Example

This statement enables query rewrite in all sessions for all materialized views that have not been explicitly disabled:

ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;

Restricted Session Example

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

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;

Shared Pool Example

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;

CHECKPOINT Example

The following statement forces a checkpoint:

ALTER SYSTEM CHECKPOINT;

Resource Limit Example

This ALTER SYSTEM statement dynamically enables resource limits:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Multi-Threaded Server Examples

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.

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 = 
      '(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=5)',
      '(INDEX=1)(PROTOCOL=DECNet)(DISPATCHERS=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.

Licensing Examples

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.

The following statement dynamically disables the limit for sessions on your instance. After you issue the above statement, Oracle no longer limits the number of sessions on your instance.

ALTER SYSTEM SET LICENSE_MAX_SESSIONS = 0;

The following statement dynamically changes the limit on the number of users in the database to 200. After you issue the above statement, Oracle prevents the number of users in the database from exceeding 200.

ALTER SYSTEM SET LICENSE_MAX_USERS = 200;

SWITCH LOGFILE Example

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. The following statement forces a log switch:

ALTER SYSTEM
   SWITCH LOGFILE;

Distributed Recovery Example

The following statement enables distributed recovery:

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

You may want to disable distributed recovery for demonstration or testing purposes.You can disable distributed recovery in both single-process and multiprocess mode with the following statement:

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

When your demonstration or testing are complete, you can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.

KILL SESSION Example

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

DISCONNECT SESSION 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;

See Also:
Oracle8i Parallel Server Concepts and Oracle8i Designing and Tuning for Performance for more information about application failover.

*archive_log_clause*	manually archives redo log files or enables or disables automatic archiving. To use this clause, your instance must have the database mounted. The database can be either open or closed unless otherwise noted.
	Notes: You can also manually archive redo log file groups with the `ARCHIVE LOG` SQLPlus statement. For information on this statement, see the SQLPlus User's Guide and Reference. You can also have Oracle archive redo log files groups automatically. For information on automatic archiving, see Oracle8i Administrator's Guide. You can always manually archive redo log file groups regardless of whether automatic archiving is enabled.
	`THREAD`	specifies the thread containing the redo log file group to be archived. Set this parameter only if you are using Oracle with the Parallel Server option in parallel mode.
	`SEQUENCE`	manually archives the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the `THREAD` parameter, Oracle archives the specified group from the thread assigned to your instance.
	`CHANGE`	manually archives the online redo log file group containing the redo log entry with the system change number (SCN) specified by integer in the specified thread. If the SCN is in the current redo log file group, Oracle performs a log switch. If you omit the `THREAD` parameter, Oracle archives the groups containing this SCN from all enabled threads. You can use this clause only when your instance has the database open.
	`CURRENT`	manually archives the current redo log file group of the specified thread, forcing a log switch. If you omit the `THREAD` parameter, Oracle archives all redo log file groups from all enabled threads, including logs previous to current logs. You can use this clause only when your instance has the database open.
	Note: If you specify a redo log file group for archiving with the `CHANGE` or `CURRENT` clause, and earlier redo log file groups are not yet archived, Oracle archives all unarchived groups up to and including the specified group.
	`GROUP`	manually archives the online redo log file group with the `GROUP` value specified by integer. You can determine the `GROUP` value for a redo log file group by examining the data dictionary view `DBA_LOG_FILES`. If you specify both the `THREAD` and `GROUP` parameters, the specified redo log file group must be in the specified thread.
	`LOGFILE`	manually archives the online redo log file group containing the redo log file member identified by 'filename'. If you specify both the `THREAD` and `LOGFILE` parameters, the specified redo log file group must be in the specified thread.
		Restriction: You must archive redo log file groups in the order in which they are filled. If you specify a redo log file group for archiving with the `LOGFILE` parameter, and earlier redo log file groups are not yet archived, Oracle returns an error.
	`NEXT`	manually archives the next online redo log file group from the specified thread that is full but has not yet been archived. If you omit the `THREAD` parameter, Oracle archives the earliest unarchived redo log file group from any enabled thread.
	`ALL`	manually archives all online redo log file groups from the specified thread that are full but have not been archived. If you omit the `THREAD` parameter, Oracle archives all full unarchived redo log file groups from all enabled threads.
	`START`	enables automatic archiving of redo log file groups. Restriction: You can enable automatic archiving only for the thread assigned to your instance.
	`TO` 'location'	specifies the primary location to which the redo log file groups are archived. The value of this parameter must be a fully specified file location following the conventions of your operating system. If you omit this parameter, Oracle archives the redo log file group to the location specified by the initialization parameters `LOG_ARCHIVE_DEST` or `LOG_ARCHIVE_DEST_`n.
		Note: You can enhance recovery reliability by setting the related archive parameters `LOG_ARCHIVE_DEST_DUPLEX` and `LOG_ARCHIVE_MIN_SUCCEED_DEST`.
	`STOP`	disables automatic archiving of redo log file groups. You can disable automatic archiving only for the thread assigned to your instance.
`CHECKPOINT`	explicitly forces Oracle to perform a checkpoint, ensuring that all changes made by committed transactions are written to datafiles on disk. You can specify this clause only when your instance has the database open. Oracle does not return control to you until the checkpoint is complete.
	`GLOBAL`	in an Oracle Parallel Server environment, performs a checkpoint for all instances that have opened the database. This is the default.
	`LOCAL`	in an Oracle Parallel Server environment, performs a checkpoint only for the thread of redo log file groups for your instance.
	See Also: Oracle8i Concepts for more information on checkpoints.
`CHECK DATAFILES`	in a distributed database system, such as an Oracle Parallel Server environment, updates an instance's SGA from the database control file to reflect information on all online datafiles.
	`GLOBAL`	performs this synchronization for all instances that have opened the database. This is the default.
	`LOCAL`	performs this synchronization only for the local instance.
	Your instance should have the database open. See Also: Oracle8i Parallel Server Setup and Configuration Guide.
`DISCONNECT SESSION`	disconnects the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a multi-threaded server). To use this clause, your instance must have the database open. 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.
	If system parameters are appropriately configured, application failover will take effect. See Also: Oracle8i Parallel Server Setup and Configuration Guide for more information about application failover.
	`POST_TRANSACTION`	allows ongoing transactions to complete before the session is disconnected. If the session has no ongoing transactions, this clause has the same effect as KILL SESSION, described below.
	`IMMEDIATE`	disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete. If you also specify `POST_TRANSACTION` and the session has ongoing transactions, the `IMMEDIATE` keyword is ignored. If you do not specify `POST_TRANSACTION`, or you specify `POST_TRANSACTION` but the session has no ongoing transactions, this clause has the same effect as `KILL` `SESSION` `IMMEDIATE`, described below.
`DISTRIBUTED RECOVERY`	specifies whether or not distributed recovery is enabled. To use this clause, your instance must have the database open.
	`ENABLE`	enables distributed recovery. In a single-process environment, you must use this clause to initiate distributed recovery.
		You may need to issue the E`NABLE DISTRIBUTED RECOVERY` statement more than once to recover an in-doubt transaction if the remote node involved in the transaction is not accessible. In-doubt transactions appear in the data dictionary view `DBA_2PC_PENDING`. See Also: Oracle8i Distributed Database Systems for more information about distributed transactions and distributed recovery.
	`DISABLE`	disables distributed recovery.
`RESTRICTED SESSION`	specifies whether logon to Oracle is restricted
	`ENABLE`	allows only users with `RESTRICTED SESSION` system privilege to log on to Oracle. Existing sessions are not terminated.
	`DISABLE`	reverses the effect of the `ENABLE RESTRICTED SESSION` clause, allowing all users with `CREATE SESSION` system privilege to log on to Oracle. This is the default.
	You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
`FLUSH SHARED_POOL`	clears all data from the shared pool in the system global area (SGA). The shared pool stores Cached data dictionary information and Shared SQL and PL/SQL areas for SQL statements, stored procedures, function, packages, and triggers. This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
`KILL` `SESSION`	marks a session as dead, rolls back ongoing transactions, releases all session locks, and partially recovers session resources. To use this clause, your instance must have the database open, and your session and the session to be killed must be on the same instance. You must identify the session with both of the following values from the `V$SESSION` view: