Release 3 (8.1.7)
Part Number A85397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 |
|
SQL Statements:
ALTER CLUSTER to ALTER SYSTEM, 19 of 19
Use the ALTER SYSTEM statement 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.
end_session_clauses::=
set_clause::=
archive_log_clause
The 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.
|
See Also:
|
CHECKPOINT
Specify CHECKPOINT to explicitly force 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, this setting causes Oracle to perform a checkpoint for all instances that have opened the database. This is the default. |
|
|
|
In an Oracle Parallel Server environment, this setting causes Oracle to performs a checkpoint only for the thread of redo log file groups for your instance. |
|
|
||
CHECK DATAFILES In a distributed database system, such as an Oracle Parallel Server environment, this clause updates an instance's SGA from the database control file to reflect information on all online datafiles.
Your instance should have the database open.
end_session_clauses|
|
Use the |
|
|
|
integer1 |
The first integer is the value of the |
|
|
integer2 |
The second integer is the value of the |
|
|
If system parameters are appropriately configured, application failover will take effect.
|
|
|
|
|
The |
|
|
|
The
|
|
|
The |
|
|
|
|
The first integer is the value of the |
|
|
|
The second 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. |
|
|
|
|
Specify |
DISTRIBUTED RECOVERY
The DISTRIBUTED RECOVERY clause lets you enable or disable distributed recovery. To use this clause, your instance must have the database open.
|
|
Specify |
|
|
|
You may need to issue the
|
|
|
|
Specify |
|
RESTRICTED SESSION
The RESTRICTED SESSION clause lets you restrict logon to Oracle.
You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.
FLUSH SHARED_POOL
The FLUSH SHARED POOL clause lets you clear 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.
SWITCH LOGFILE
The SWITCH LOGFILE clause lets you explicitly force 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.
SUSPEND | RESUME |
|
The |
|
|
|
Restrictions: |
|
|
|
The
|
|
SHUTDOWN
The SHUTDOWN 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 NAME column of the V$DISPATCHER dynamic performance view.)
IMMEDIATE, the dispatcher stops accepting new connections immediately and Oracle terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process literally shuts down.
IMMEDIATE, the dispatcher stops accepting new connections immediately but waits for all its users to disconnect and for all its database links to terminate. Then it literally shuts down.
|
See Also: Oracle8i Administrator's Guide, Net8 Administrator's Guide, and Oracle8i Performance Guide and Reference for more information on dispatchers and multi-threaded server architecture |
set_clause
The set_clause lets you set the system parameters that follow. You can set values for multiple parameters in the same set_clause.
|
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. |
AQ_TM_PROCESSES = integer
AQ_TM_PROCESSES is an Advanced Queuing parameter that specifies whether a queue monitor process is created. Accepted values are 1 (creates one queue monitor process to monitor messages) and 0 (kills any existing queue monitor processes, whether they were created using an initialization parameter or another ALTER SYSTEM statement). You can create up to 10 queue monitor processes if you use this parameter in an initialization parameter file.
BACKGROUND_DUMP_DEST = 'text'
The BACKGROUND_DUMP_DEST parameter specifies the pathname for a directory where debugging trace files for the background processes are written during Oracle operations.
BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED
The BACKUP_TAPE_IO_SLAVES parameter lets you specify whether I/O slaves are used by the Recovery Manager to back up, copy, or restore data to tape.
CONTROL_FILE_RECORD_KEEP_TIME = integer
The CONTROL_FILE_RECORD_KEEP_TIME parameter lets you specify the minimum of days before a reusable record in the control file can be reused.
CORE_DUMP_DEST = 'text'
The CORE_DUMP_DEST parameter lets you specify the directory where Oracle dumps core files.
CREATE_STORED_OUTLINES = { true | false | 'category_name' } [nooverride]
The CREATE_STORED_OUTLINES parameter determines whether Oracle should automatically create and store an outline for each query submitted on the system. CREATE_STORED_OUTLINES is not an initialization parameter.
true enables automatic outline creation for subsequent queries in the system. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, that outline will remain and a new outline will not be created.
false disables automatic outline creation for the system. This is the default.
category_name has the same behavior as true except that any outline created in the system is stored in the category_name category.
nooverride specifies that this system setting will not override the setting for any session in which this parameter was explicitly set. If you do not specify nooverride, this setting takes effect in all sessions.
CURSOR_SHARING = {force | exact}
The CURSOR_SHARING parameter determines what kind of SQL statements can share the same cursors.
exact causes only identical SQL statements to share a cursor.
force forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.
|
See Also: Oracle8i Performance Guide and Reference for information on setting this parameter in these and other environments |
DB_BLOCK_CHECKING = {true | false} deferred
The DB_BLOCK_CHECKING parameter controls whether data block checking is done. The default is false, for compatibility with earlier releases where block checking is disabled as a default.
DB_BLOCK_CHECKSUM = {true | false}
The DB_BLOCK_CHECKSUM parameter determines 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.
DB_BLOCK_MAX_DIRTY_TARGET = integer
The DB_BLOCK_MAX_DIRTY_TARGET parameter 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.
|
See Also:
|
DB_FILE_DIRECT_IO_COUNT = integer deferred
The DB_FILE_DIRECT_IO_COUNT parameter determines the number of blocks Oracle should use for I/O during backup, restore, or direct-path read and write operations.
DB_FILE_MULTIBLOCK_READ_COUNT = integer
The DB_FILE_MULTIBLOCK_READ_COUNT parameter determines the maximum number of blocks read in one I/O operation during a sequential scan.
FAST_START_IO_TARGET = integer
The FAST_START_IO_TARGET determines 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.
FAST_START_PARALLEL_ROLLBACK = { false | low | high}
The FAST_START_PARALLEL_ROLLBACK parameter determines the number of processes spawned to perform parallel recovery.
false specifies no parallel recovery. SMON will serially recover dead transactions.
low specifies that the number of recovery servers may not exceed twice the value of the CPU_COUNT parameter.
high specifies that the number of recovery servers may not exceed four times the value of the CPU_COUNT parameter.
FIXED_DATE = { 'DD_MM_YY' | 'YYYY_MI_DD_HH24_MI-SS' }
The FIXED_DATE lets you specify a constant date for SYSDATE instead of the current date.
GC_DEFER_TIME = integer
The GC_DEFER_TIME parameter lets you specify the time (in hundredths of seconds) that Oracle waits before responding to forced-write requests from other instances.
GLOBAL_NAMES = {true | false}
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. This system parameter enables or disables global name resolution while your instance is running. A setting of true enables the enforcement of global names. A setting of false disables the enforcement of global names. You can also enable or disable global name resolution for your session with the GLOBAL_NAMES parameter of the ALTER SESSION statement.
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 |
HASH_MULTIBLOCK_IO_COUNT = integer
The HASH_MULTIBLOCK_IO_COUNT parameter determines the number of data blocks Oracle reads and writes during a hash join operation. The value multiplied by the DB_BLOCK_SIZE initialization parameter should not exceed 64K. The default value for this parameter is 1. If the multi-threaded server is used, the value is always 1, and any value given here is ignored.
HS_AUTOREGISTER = {true | false}
The HS_AUTOREGISTER lets you enable or disable 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 |
JOB_QUEUE_PROCESSES = integer
The JOB_QUEUE_PROCESSES determines 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.
LICENSE_MAX_SESSIONS = integer
The LICENSE_MAX_SESSIONS parameter lets you reset (for the current instance) the value of the initialization parameter LICENSE_MAX_SESSIONS, which 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. A value of 0 disables the limit.
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 RESTRICTED SESSION system privilege can begin new sessions only when the number of sessions falls below the new limit.
LICENSE_MAX_USERS = integer
The LICENSE_MAX_USERS parameter lets you reset (for the current instance) the value of the initialization parameter LICENSE_MAX_USERS, which establishes the limit for users connected to your database. Once this limit is reached, more users cannot connect. A value of 0 disables the limit.
Restriction: You cannot reduce the limit on users below the current number of users created for the database.
LICENSE_SESSIONS_WARNING = integer
The LICENSE_SESSIONS_WARNING parameter lets you reset (for the current instance) the value of the initialization parameter LICENSE_SESSIONS_WARNING, which 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 RESTICTED SESSION system privilege receive warning messages when they begin subsequent sessions. A value of 0 disables the warning threshold.
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.
LOG_ARCHIVE_DEST = string
The LOG_ARCHIVE_DEST parameter lets you specify 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:
LOG_ARCHIVE_DEST_n in your initialization parameter file, nor can you set a value for that parameter using the ALTER SESSION or ALTER SYSTEM statement.
LOG_ARCHIVE_MIN_SUCCEED_DEST using the ALTER SESSION statement.
|
See Also: The |
LOG_ARCHIVE_DEST_nLOG_ARCHIVE_DEST_n = {null_string| {LOCATION=local_pathname | SERVICE=tnsnames_service}[MANDATORY | OPTIONAL] [REOPEN[=integer]]}
The LOG_ARCHIVE_DEST_n parameter lets you specify 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:
LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST in your initialization parameter file, nor can you set values for those parameters using the ALTER SYSTEM statement.
ALTER SYSTEM ARCHIVE LOG TO location statement.
|
See Also: The |
LOG_ARCHIVE_DEST_STATE_n = {enable | defer}
The LOG_ARCHIVE_DEST_STATE_n parameter lets you specify the state associated with the corresponding LOG_ARCHIVE_DEST_n parameter.
enable specifies that any associated valid destination can be used for archiving. This is the default.
defer specifies that Oracle will not consider for archiving any destination associated with the corresponding LOG_ARCHIVE_DEST_n parameter.
LOG_ARCHIVE_DUPLEX_DEST = string
The LOG_ARCHIVE_DUPLEX_DEST parameter lets you specify 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:
LOG_ARCHIVE_DEST.
LOG_ARCHIVE_DEST_n in your initialization parameter file, nor can you set a value for that parameter using the ALTER SYSTEM or ALTER SESSION statement.
LOG_ARCHIVE_MIN_SUCCEED_DEST using the ALTER SESSION statement.
LOG_ARCHIVE_MAX_PROCESSES = integer
The LOG_ARCHIVE_MAX_PROCESSES lets you specify the number of archiver processes that are invoked. Permitted values are integers 1 through 10, inclusive. The default is 1.
LOG_ARCHIVE_MIN_SUCCEED_DEST = integer
The LOG_ARCHIVE_MIN_SUCCEED_DEST parameter lets you specify the minimum number of destinations that must succeed in order for the online log file to be available for reuse.
LOG_ARCHIVE_TRACE = integer
The LOG_ARCHIVE_TRACE parameter controls the type of output information generated by archivelog processes.
|
See Also:
|
LOG_CHECKPOINT_INTERVAL = integer
The LOG_CHECKPOINT_INTERVAL lets you limit to integer the number of redo blocks that can exist between an incremental checkpoint and the last block written to the redo log.
LOG_CHECKPOINT_TIMEOUT = integer
The LOG_CHECKPOINT_TIMEOUT parameter lets you limit 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. This parameter signifies that no buffer will remain dirty (in the cache) for more than integer seconds. The default is 1800 seconds.
MAX_DUMP_FILE_SIZE = { size | 'unlimited'} [deferred]
The MAX_DUMP_FILE_SIZE lets you specify 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.
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:
MTS_MAX_DISPATCHERS.
MTS_DISPATCHERS = 'dispatch_clause' dispatch_clause::=
(PROTOCOL = protocol) |( ADDRESS = address) |(DESCRIPTION = description )[options_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)
The MTS_DISPATCHERS parameter lets you modify or create the configuration of dispatcher processes. A description of the parameters appears in Oracle8i Reference.
You can specify multiple MTS_DISPATCHERS parameters in a single statement for multiple network protocols.
|
See Also: Oracle8i Administrator's Guide for more information on this parameter, see Net8 Administrator's Guide |
MTS_SERVERS = integer
The MTS_SERVERS parameter lets you specify a new minimum number of shared server processes.
OBJECT_CACHE_MAX_SIZE_PERCENT = integer deferred
The OBJECT_CACHE_MAX_SIZE_PERCENT parameter lets you specify the percentage of the optimal cache size that the session object cache can grow past the optimal size.
OBJECT_CACHE_OPTIMAL_SIZE = integer deferred
The OBJECT_CACHE_OPTIMAL_SIZE parameter lets you specify (in kilobytes) the size to which the session object cache is reduced if it exceeds the maximum size.
OPTIMIZER_MAX_PERMUTATIONS = integer nooverride
The OPTIMIZER_MAX_PERMUTATIONS parameter 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.
nooverride specifies that this system setting will not override the setting for any session in which this parameter was explicitly set.
PARALLEL_ADAPTIVE_MULTI_USER = {true | false}
The PARALLEL_ADAPTIVE_MULTI_USER parameter lets you specify that Oracle should vary the degree of parallelism based on the total perceived load on the system.
PARALLEL_INSTANCE_GROUP = 'text'
The PARALLEL_INSTANCE_GROUP parameter lets you specify the name of the Oracle Parallel Server instance group to be used for spawning parallel query slaves.
PARALLEL_THREADS_PER_CPU = integer
Use the PARALLEL_THREADS_PER_CPU parameter to specify the degree of parallelism for parallel operations where the degree of parallelism is unset. The default is operating system dependent.
PLSQL_V2_COMPATIBILITY = {true | false} [deferred]
Use the PLSQL_V2_COMPATIBILITY parameter to modify 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).
true to enable Oracle8i PL/SQL V3 programs to execute Oracle7 PL/SQL V2 constructs.
false to disallow illegal Oracle7 PL/SQL V2 constructs. This is the default.
|
See Also: PL/SQL User's Guide and Reference and Oracle8i Reference for more information about this system parameter |
QUERY_REWRITE_ENABLED = { true | false } [deferred | nooverride]
The QUERY_REWRITE_ENABLED parameter lets you enable or disable query rewrite on all materialized views that have not been explicitly disabled. By default, true enables query rewrite for all sessions immediately. Query rewrite is superseded and disabled by rule-based optimization (that is, if the OPTIMIZER_MODE parameter is set to rule). Also enables or disables use of any function-based indexes defined on the materialized view.
deferred specifies that query rewrite is enabled or disabled only for future sessions.
nooverride specifies that query rewrite is enabled or disabled for all sessions that have not explicitly set this parameter using ALTER SESSION.
true setting has no effect on materialized views that cannot be created with the ENABLE QUERY REWRITE clause, such as materialized views created totally or in part from a view.
QUERY_REWRITE_INTEGRITYQUERY_REWRITE_INTEGRITY ={ enforced | trusted | stale_tolerated }
The QUERY_REWRITE_INTEGRITY parameter lets you set the minimum consistency level for query rewrite for the duration of the instance. The following values are permitted:
enforced is the safest level. It relies only on system-enforced relationships so that data integrity and correctness can be guaranteed. This level ensures that query rewrite will not use any function-based index or any materialized view that includes a call to a user-defined function.
In addition, this level ensures that query rewrite will not use any dimensional information or any constraints enabled with the RELY keyword.
trusted specifies that materialized views created with the ON PREBUILT TABLE clause are supported, and trusted but unenforced join relationships are accepted. Query rewrite uses join information from dimensions and enables unenforced constraints with the RELY keyword.
stale_tolerated specifies that any stale, usable materialized view may be used.
This parameter does not affect descending indexes.
|
See Also:
|
REMOTE_DEPENDENCIES_MODE = {timestamp | signature}
The REMOTE_DEPENDENCIES_MODE paraleter lets you specify how dependencies of remote stored procedures are handled by the server.
RESOURCE_LIMIT = {true | false}
When you start an instance, Oracle enforces resource limits assigned to users based on the value of the RESOURCE_LIMIT initialization parameter. This system parameter enables or disables resource limits for subsequent sessions. true enables resource limits. false disables resource limits.
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.
RESOURCE_MANAGER_PLAN = plan_name
The RESOURCE_MANAGER_PLAN parameter lets you specify the name of the resource plan Oracle should use to allocate system resources among resource consumer groups.
|
See Also: Oracle8i Administrator's Guide for information on resource consumer groups and resource plans |
SORT_AREA_RETAINED_SIZE = integer deferred
The SORT_AREA_RETAINED_SIZE parameter lets you specify (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 SORT_AREA_SIZE parameter.
SORT_AREA_SIZE = integer deferred
The SORT_AREA_SIZE parameter lets you specify (in bytes) the maximum amount of memory to use for each sort operation. The default is operating system dependent.
SORT_MULTIBLOCK_READ_COUNT = integer deferred
The SORT_MULTIBLOCK_READ_COUNT parameter lets you specify the number of database blocks to read each time a sort performs a read from temporary segments. The default is 2.
STANDBY_ARCHIVE_DEST = string
The STANDBY_ARCHIVE_DEST parameter lets you specify a valid operating system pathname as the standby database destination for the archive redo log files.
TIMED_STATISTICS = {true | false}
The TIMED_STATISTICS parameter lets you specify whether the server requests the time from the operating system when generating time-related statistics. The default is false.
TIMED_OS_STATISTICS = integer
The TIMED_OS_STATISTICS lets you specify that operating system statistics will be collected when a request is made from a client to the server or when a request completes.
TRANSACTION_AUDITING = {true | false} deferred
The TRANSACTION_AUDITING parameter lets you specify whether the transaction layer generates a special redo record containing session and user information.
USE_STORED_OUTLINES = { true | false | 'category_name' } [nooverride]
The USE_STORED_OUTLINES parameter determines whether the optimizer will use stored outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.
true causes the optimizer to use outlines stored in the DEFAULT category when compiling requests.
false specifies that the optimizer should not use stored outlines. This is the default.
category_name causes the optimizer to use outlines stored in the category_name category when compiling requests.
nooverride specifies that this system setting will not override the setting for any session in which this parameter was explicitly set. If you do not specify nooverride, this setting takes effect in all sessions.
USER_DUMP_DEST = 'directory_name'
The USER_DUMP_DEST parameter lets you specify 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#, usernameFROM v$sessionSID 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 Performance Guide and Reference for more information about application failover |
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|