Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 5 of 5
end_session_clauses::=
To dynamically alter your Oracle instance. The settings stay in effect as long as the database is mounted.
You must have ALTER
SYSTEM
system privilege.
To specify the archive_log_clause, you must have the OSDBA or OSOPER role enabled.
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:
|
|
|
|
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. |
|
|
manually archives the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the |
|
|
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 |
|
|
manually archives the current redo log file group of the specified thread, forcing a log switch. If you omit the |
|
Note: If you specify a redo log file group for archiving with the |
|
|
|
manually archives the online redo log file group with the |
|
|
manually archives the online redo log file group containing the redo log file member identified by 'filename'. If you specify both the |
|
|
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 |
|
|
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 |
|
|
manually archives all online redo log file groups from the specified thread that are full but have not been archived. If you omit the |
|
|
enables automatic archiving of redo log file groups. Restriction: You can enable automatic archiving only for the thread assigned to your instance. |
|
|
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 |
|
|
Note: You can enhance recovery reliability by setting the related archive parameters |
|
|
disables automatic archiving of redo log file groups. You can disable automatic archiving only for the thread assigned to your instance. |
|
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. |
|
|
|
in an Oracle Parallel Server environment, performs a checkpoint for all instances that have opened the database. This is the default. |
|
|
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. |
|
|
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. |
|
|
|
performs this synchronization for all instances that have opened the database. This is the default. |
|
|
performs this synchronization only for the local instance. |
|
Your instance should have the database open. See Also: Oracle8i Parallel Server Setup and Configuration Guide. |
|
|
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 |
|
|
integer1 |
is the value of the |
|
integer2 |
is the value of the |
|
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. |
|
|
|
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. |
|
|
disconnects the session and recovers the entire session state immediately, without waiting for ongoing transactions to complete.
|
|
specifies whether or not distributed recovery is enabled. To use this clause, your instance must have the database open. |
|
|
|
enables distributed recovery. In a single-process environment, you must use this clause to initiate distributed recovery. |
|
|
You may need to issue the E See Also: Oracle8i Distributed Database Systems for more information about distributed transactions and distributed recovery. |
|
|
disables distributed recovery. |
|
specifies whether logon to Oracle is restricted |
|
|
|
allows only users with |
|
|
reverses the effect of the |
|
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed. |
|
|
clears all data from the shared pool in the system global area (SGA). The shared pool stores
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. |
|
|
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 |
|
|
integer1 |
is the value of the |
|
integer2 |
is the value of the |
|
If the session 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, marks the session as dead, and then returns control to you. If the waiting lasts a minute, Oracle marks the session to be killed and returns control to you with a message that the session is marked to be killed. The PMON background process then marks the session as dead when the activity is complete. |
|
|
Whether or not the session has an ongoing transaction, Oracle does not recover the entire session state until the session user issues a request to the session and receives a message that the session has been killed. |
|
|
|
rolls back ongoing transactions, releases all session locks, recovers the entire session state, and returns control to you immediately. |
|
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. When you force a log switch, Oracle begins to perform a checkpoint. Oracle returns control to you immediately rather than when the checkpoint is complete. To use this clause, your instance must have the database open. |
|
|
suspends all I/O (datafile, control file, and file header) as well as queries, in all instances, enabling you to make copies of the database without having to handle ongoing transactions. |
|
|
Restrictions: |
|
|
makes the database available once again for queries and I/O.
See Also: Oracle8i Backup and Recovery Guide for more information on the |
|
|
This clause is relevant only if your system is using Oracle's multi-threaded server architecture. It shuts down a dispatcher identified by dispatcher_name. The dispatcher_name must be a string of the form 'Dxxx', where xxx indicates the number of the dispatcher. (For a listing of dispatcher names, query the
See Also: Oracle8i Administrator's Guide, Net8 Administrator's Guide, and Oracle8i Designing and Tuning for Performance for more information on dispatchers and multi-threaded server architecture. |
|
set_clause |
sets the system parameters that follow. You can set values for multiple parameters in the same set_clause.The |
|
|
CAUTION: Unless otherwise noted, these parameters are initialization parameters, and the descriptions provided here indicate only the general nature of the parameters. Before changing the values of initialization parameters, please refer to their full description in Oracle8i Reference and Oracle8i National Language Support Guide. |
|
|
||
|
is an Advanced Queuing parameter that specifies whether a time manager process is created. Accepted values are 1 (creates one time manager process to monitor messages) and 0 (does not create a time manager process). |
|
|
||
|
specifies the pathname for a directory where debugging trace files for the background processes are written during Oracle operations. |
|
|
||
|
specifies whether I/O slaves are used by the Recovery Manager to back up, copy, or restore data to tape. |
|
|
||
|
specifies the minimum of days before a reusable record in the control file can be reused. |
|
|
||
|
specifies the directory where Oracle dumps core files. |
|
|
||
|
determines whether Oracle should automatically create and store an outline for each query submitted on the system. |
|
|
|
|
|
|
|
|
||
|
determines what kind of SQL statements can share the same cursors.
See Also: for information on setting this parameter in these and other environments, see Oracle8i Designing and Tuning for Performance. |
|
|
||
|
controls whether data block checking is done. The default is |
|
|
||
|
specifies whether the database writer background process and the direct loader will calculate a checksum and store it in the cache header of every data lock when writing to disk. |
|
|
||
|
limits to integer the number of dirty buffers in the cache and reduces the number of buffers that will need to be read during crash or instance recovery. This parameter does not relate to media recovery. A value of 0 disables this parameter. The minimum accepted value to enable the parameter is 1000. |
|
|
Note: Oracle Corporation recommends that Enterprise Edition users who were using incremental checkpointing in an earlier release now use fast-start checkpointing in Oracle8i. In fast-start checkpointing, the |
|
|
||
|
specifies the number of blocks Oracle should use for I/O during backup, restore, or direct-path read and write operations. |
|
|
||
|
specifies the maximum number of blocks read in one I/O operation during a sequential scan. |
|
|
||
|
specifies the target number of I/Os (reads and writes) to and from buffer cache that Oracle should perform upon crash or instance recovery. Oracle continuously calculates the actual number of I/Os that would be needed for recovery and compares that number against the target. If the actual number is greater than the target, Oracle attempts to write additional dirty buffers to advance the checkpoint, while minimizing the affect on performance. For information on how to tune this parameter, see Oracle8i Designing and Tuning for Performance. |
|
|
||
|
specifies the number of processes spawned to perform parallel recovery.
|
|
|
||
|
specifies a constant date for |
|
|
||
|
specifies the time (in hundredths of seconds) that Oracle waits before responding to forced-write requests from other instances. |
|
|
||
|
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 |
|
|
Oracle recommends that you enable global name resolution if you use or plan to use distributed processing. See Also: "Referring to Objects in Remote Databases" and Oracle8i Distributed Database Systems for more information on global name resolution and how Oracle enforces it. |
|
|
||
|
specifies the number of data blocks to read and write during a hash join operation. The value multiplied by the |
|
|
||
|
enables or disables automatic self-registration of non-Oracle system characteristics in the Oracle server's data dictionary by Heterogeneous Services agents. See Also: Oracle8i Distributed Database Systems for more information on accessing non-Oracle systems through Heterogeneous Services. |
|
|
||
|
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 See Also: Oracle8i Replication for more information on managing table snapshots. |
|
|
||
|
resets (for the current instance) the value of the initialization parameter |
|
|
If you reduce the limit on sessions below the current number of sessions, Oracle does not end existing sessions to enforce the new limit. However, users without |
|
|
Do not disable or raise session limits unless you have appropriately upgraded your Oracle license. For more information, contact your Oracle sales representative. |
|
|
||
|
resets (for the current instance) the value of the initialization parameter |
|
|
Restriction: You cannot reduce the limit on users below the current number of users created for the database. Do not disable or raise user limits unless you have appropriately upgraded your Oracle license. For more information, contact your Oracle sales representative. |
|
|
||
|
resets (for the current instance) the value of the initialization parameter |
|
|
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. |
|
|
||
|
specifies a valid operating system pathname as the primary destination for all archive redo log file groups. |
|
|
Restrictions: If you set a value for this parameter: |
|
|
||
|
specifies up to five valid operating system pathnames or Oracle service names (plus other related options) as destinations for archive redo log file groups (n = integers 1 through 5). For a description of the options, refer to Oracle8i Reference. |
|
|
Restrictions: If you set a value for this parameter:
|
|
|
||
|
specifies the state associated with the corresponding |
|
|
||
|
specifies a valid operating system pathname as the secondary destination for all archive redo log file groups. |
|
|
Restriction: If you set a value for this parameter:
|
|
|
||
|
specifies the number of archiver processes that are invoked. Permitted values are integers 1 through 10, inclusive. The default is 1. |
|
|
||
|
specifies the minimum number of destinations that must succeed in order for the online log file to be available for reuse. |
|
|
||
|
controls the type of output information generated by archivelog processes. See Also:
|
|
|
||
|
limits to integer the number of redo blocks that can exist between an incremental checkpoint and the last block written to the redo log. |
|
|
||
|
limits the incremental checkpoint to be at the position where the last write to the redo log (sometimes called the "tail of the log") was integer seconds ago, and signifies that no buffer will remain dirty (in the cache) for more than integer seconds. The default is 1800 seconds. |
|
|
||
|
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 ' |
|
Multi-Threaded Server Parameters: When you start your instance, Oracle creates shared server processes and dispatcher processes for the multi-threaded server architecture based on the values of the MTS_SERVERS and MTS_DISPATCHERS initialization parameters. You can set the MTS_SERVERS and MTS_DISPATCHERS session parameters to perform one of the following operations while the instance is running: |
||
|
||
See Also: |
||
|
||
|
dispatch_clause::= ( |
|
|
options_clause::= (DISPATCHERS = integer | SESSIONS = integer | CONNECTIONS = integer | TICKS = seconds | POOL = { 1 | ON | YES | TRUE | BOTH | ({IN|OUT} = ticks) | 0 | OFF | NO | FALSE | ticks} | MULTIPLEX = {1 | ON | YES | TRUE | 0 | OFF | NO | FALSE | BOTH | IN | OUT} | LISTENER = tnsname | SERVICE = service | INDEX = integer) |
|
|
modifies or creates the configuration of dispatcher processes. A description of the parameters appears in Oracle8i Reference. |
|
|
You can specify multiple See Also: Oracle8i Administrator's Guide for more information on this parameter, see Net8 Administrator's Guide. |
|
|
||
|
specifies a new minimum number of shared server processes. |
|
|
||
|
specifies the percentage of the optimal cache size that the session object cache can grow past the optimal size. |
|
|
||
|
specifies (in kilobytes) the size to which the session object cache is reduced if it exceeds the maximum size. |
|
|
||
|
lets you limit the amount of work the optimizer expends on optimizing queries with large joins. The value of integer is the number of permutations of the tables the optimizer will consider with large joins.
|
|
|
||
|
specifies that Oracle should vary the degree of parallelism based on the total perceived load on the system. |
|
|
||
|
specifies the name of the Oracle Parallel Server instance group to be used for spawning parallel query slaves. |
|
|
||
|
used to compute the degree of parallelism for parallel operations where the degree of parallelism is unset. The default is operating system dependent. |
|
|
||
|
modifies the compile-time behavior of PL/SQL programs to allow language constructs that are illegal in Oracle8 and Oracle8i (PL/SQL V3), but were legal in Oracle7 (PL/SQL V2). See PL/SQL User's Guide and Reference and Oracle8i Reference for more information about this system parameter. |
|
|
|
enables Oracle8i PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs. |
|
|
disallows illegal Oracle7 PL/SQL V2 constructs. This is the default. |
|
||
|
enables or disables query rewrite on all materialized views that have not been explicitly disabled. By default, See Also: Oracle8i Data Warehousing Guide for more information on query rewrite. |
|
|
||
|
|
|
|
||
|
sets the minimum consistency level for query rewrite for the duration of the instance. The following values are permitted: |
|
|
This parameter does not affect descending indexes. See Also:
|
|
|
||
|
specifies how dependencies of remote stored procedures are handled by the server. See Also: Oracle8i Application Developer's Guide - Fundamentals. |
|
|
||
|
When you start an instance, Oracle enforces resource limits assigned to users based on the value of the |
|
|
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 and assign that profile to the user. See Also: "CREATE PROFILE" and "CREATE USER". |
|
|
||
|
specifies the name of the resource plan Oracle should use to allocate system resources among resource consumer groups. For information on resource consumer groups and resource plans, refer to Oracle8i Administrator's Guide. |
|
|
||
|
specifies (in bytes) the maximum amount of memory that each sort operation will retain after the first fetch is done, until the cursor ends. If you do not explicitly set this parameter in the initialization parameter file or dynamically, Oracle uses the value of the |
|
|
||
|
specifies (in bytes) the maximum amount of memory to use for each sort operation. The default is operating system dependent. |
|
|
||
|
specifies the number of database blocks to read each time a sort performs a read from temporary segments. The default is 2. |
|
|
||
|
specifies a valid operating system pathname as the standby database destination for the archive redo log files. |
|
|
||
|
specifies whether the server requests the time from the operating system when generating time-related statistics. The default is |
|
|
||
|
specifies that operating system statistics will be collected when a request is made from a client to the server or when a request completes. |
|
|
||
|
specifies whether the transaction layer generates a special redo record containing session and user information. |
|
|
||
|
determines whether the optimizer will use stored outlines to generate execution plans. |
|
|
||
|
||
|
||
|
specifies the pathname where Oracle will write debugging trace files on behalf of a user process. |
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$]';
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;
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;
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 following statement forces a checkpoint:
ALTER SYSTEM CHECKPOINT;
This ALTER SYSTEM
statement dynamically enables resource limits:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
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.
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;
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;
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.
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';
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. |
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|