Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

ALTER SYSTEM

Purpose

Use the ALTER SYSTEM statement to dynamically alter your Oracle Database instance. The settings stay in effect as long as the database is mounted.

Prerequisites

You must have ALTER SYSTEM system privilege.

Syntax


alter_system::=
Description of alter_system.gif follows
Description of the illustration alter_system.gif

(archive_log_clause::=, checkpoint_clause::=, check_datafiles_clause::=, distributed_recov_clauses::=, restricted_session_clauses::=, end_session_clauses::=, quiesce_clauses::=, shutdown_dispatcher_clause::=, alter_system_set_clause ::=, alter_system_reset_clause::=)


archive_log_clause ::=
Description of archive_log_clause.gif follows
Description of the illustration archive_log_clause.gif


checkpoint_clause::=
Description of checkpoint_clause.gif follows
Description of the illustration checkpoint_clause.gif


check_datafiles_clause::=
Description of check_datafiles_clause.gif follows
Description of the illustration check_datafiles_clause.gif


distributed_recov_clauses::=
Description of distributed_recov_clauses.gif follows
Description of the illustration distributed_recov_clauses.gif


restricted_session_clauses::=
Description of restricted_session_clauses.gif follows
Description of the illustration restricted_session_clauses.gif


end_session_clauses::=
Description of end_session_clauses.gif follows
Description of the illustration end_session_clauses.gif


quiesce_clauses::=
Description of quiesce_clauses.gif follows
Description of the illustration quiesce_clauses.gif


shutdown_dispatcher_clause::=
Description of shutdown_dispatcher_clause.gif follows
Description of the illustration shutdown_dispatcher_clause.gif


alter_system_set_clause ::=
Description of alter_system_set_clause.gif follows
Description of the illustration alter_system_set_clause.gif


alter_system_reset_clause::=
Description of alter_system_reset_clause.gif follows
Description of the illustration alter_system_reset_clause.gif

Semantics


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.


INSTANCE Clause

This clause is relevant only if you are using Oracle Database with the Real Application Clusters option. Specify the name of the instance for which you want the redo log file group to be archived. The instance name is a string of up to 80 characters. Oracle Database automatically determines the thread that is mapped to the specified instance and archives the corresponding redo log file group. If no thread is mapped to the specified instance, then Oracle Database returns an error.


THREAD Clause

This clause has been deprecated, because the INSTANCE clause achieves the same purpose and is easier to use. The THREAD clause is supported for backward compatibility. Specify the number of the thread containing the redo log file group to be archived.


SEQUENCE Clause

Specify SEQUENCE to manually archive the online redo log file group identified by the log sequence number integer in the specified thread. If you omit the THREAD parameter, then Oracle Database archives the specified group from the thread assigned to your instance.


CHANGE Clause

Specify CHANGE to manually archive 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, then Oracle Database performs a log switch. If you omit the THREAD parameter, then Oracle Database archives the groups containing this SCN from all enabled threads.

You can use this clause only when your instance has the database open.


CURRENT Clause

Specify CURRENT to manually archive the current redo log file group of the specified thread, forcing a log switch. If you omit the THREAD parameter, then Oracle Database archives all redo log file groups from all enabled threads, including logs previous to current logs. You can specify CURRENT only when the database is open.


NOSWITCH

Specify NOSWITCH if you want to manually archive the current redo log file group without forcing a log switch. This setting is used primarily with standby databases to prevent data divergence when the primary database shuts down. Divergence implies the possibility of data loss in case of primary database failure.

You can use the NOSWITCH clause only when your instance has the database mounted but not open. If the database is open, then this operation closes the database automatically. You must then manually shut down the database before you can reopen it.


GROUP Clause

Specify GROUP to manually archive 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 querying the data dictionary view DBA_LOG_GROUPS. If you specify both the THREAD and GROUP parameters, then the specified redo log file group must be in the specified thread.


LOGFILE Clause

Specify LOGFILE to manually archive the online redo log file group containing the redo log file member identified by 'filename'. If you specify both the THREAD and LOGFILE parameters, then the specified redo log file group must be in the specified thread.

If the database was mounted with a backup controlfile, then specify USING BACKUP CONTROLFILE to permit archiving of all online logfiles, including the current logfile.


Restriction on the LOGFILE clause

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, then Oracle Database returns an error.


NEXT Clause

Specify NEXT to manually archive 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, then Oracle Database archives the earliest unarchived redo log file group from any enabled thread.


ALL Clause

Specify ALL to manually archive all online redo log file groups from the specified thread that are full but have not been archived. If you omit the THREAD parameter, then Oracle Database archives all full unarchived redo log file groups from all enabled threads.


START Clause

In earlier releases, this clause enabled automatic archiving of redo log file groups for the thread assigned to your instance. This clause has been deprecated, because Oracle Database automatically enables automatic archiving of redo log file groups. This clause has no effect. If you specify it, Oracle Database writes a message to the alert log.


TO location Clause

Specify TO 'location' to indicate 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, then Oracle Database archives the redo log file group to the location specified by the initialization parameters LOG_ARCHIVE_DEST or LOG_ARCHIVE_DEST_n.


STOP Clause

In earlier releases, this clause disabled automatic archiving of redo log file groups for the thread assigned to your instance. This clause has been deprecated. It has no effect, and if you specify it, Oracle Database writes a message to the alert log.


checkpoint_clause

Specify CHECKPOINT to explicitly force Oracle Database 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 Database does not return control to you until the checkpoint is complete.


GLOBAL

In a Real Application Clusters environment, this setting causes Oracle Database to perform a checkpoint for all instances that have opened the database. This is the default.


LOCAL

In a Real Application Clusters environment, this setting causes Oracle Database to perform a checkpoint only for the thread of redo log file groups for the instance from which you issue the statement.


check_datafiles_clause

In a distributed database system, such as a Real Application Clusters 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

The end_session_clauses give you several ways to end the current session.


DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process (or virtual circuit if the connection was made by way of a Shared 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:

If system parameters are appropriately configured, then application failover will take effect.


KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions, release all session locks, and partially recover session resources. To use this clause, your instance must have the database open, and your session and the session to be terminated must be on the same instance. You must identify the session with both of the following values from the V$SESSION view:

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, then Oracle Database waits for this activity to complete, marks the session as terminated, and then returns control to you. If the waiting lasts a minute, then Oracle Database marks the session to be terminated and returns control to you with a message that the session is marked to be terminated. The PMON background process then marks the session as terminated when the activity is complete.

Whether or not the session has an ongoing transaction, Oracle Database 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 terminated.


IMMEDIATE

Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions, release all session locks, recover the entire session state, and return control to you immediately.


DUMP ACTIVE SESSION HISTORY Clause

This clause lets you record in-memory active session history data into the process trace file. The data is then available for subsequent analysis by the Automatic Database Diagnostic Monitor. The default time period during which the data is collected is 10 minutes. You can override this default using the MINUTES clause.


See Also:

Oracle Database Performance Tuning Guide for more information on the Automatic Database Diagnostic Monitor and active session history


distributed_recovery_clause

The DISTRIBUTED RECOVERY clause lets you enable or disable distributed recovery. To use this clause, your instance must have the database open.


ENABLE

Specify ENABLE to enable distributed recovery. In a single-process environment, you must use this clause to initiate distributed recovery.

You may need to issue the ENABLE 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.


DISABLE

Specify DISABLE to disable distributed recovery.


restricted_session_clause

The RESTRICTED SESSION clause lets you restrict logon to Oracle Database.

You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.


ENABLE

Specify ENABLE to allow only users with RESTRICTED SESSION system privilege to log on to Oracle Database. Existing sessions are not terminated.

This clause applies only to the current instance. Therefore, in a Real Application Clusters environment, authorized users without the RESTRICTED SESSION system privilege can still access the database by way of other instances.


DISABLE

Specify DISABLE to reverse the effect of the ENABLE RESTRICTED SESSION clause, allowing all users with CREATE SESSION system privilege to log on to Oracle Database. This is the default.


FLUSH SHARED_POOL Clause

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.


FLUSH BUFFER_CACHE Clause

The FLUSH BUFFER_CACHE clause lets you clear all data from the buffer cache in the system global area (SGA).


Caution:

This clause is intended for use only on a test database. Do not use this clause on a production database, because as a result of this statement, subsequent queries will have no hits, only misses.

This clause is useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.


SWITCH LOGFILE Clause

The SWITCH LOGFILE clause lets you explicitly force Oracle Database 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 Database begins to perform a checkpoint but 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 SUSPEND clause lets you suspend 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 on SUSPEND and RESUME
  • Do not use this clause unless you have put the database tablespaces in hot backup mode.

  • If you start a new instance while the system is suspended, then that new instance will not be suspended.

The RESUME clause lets you make the database available once again for queries and I/O.


quiesce_clauses

Use the QUIESCE RESTRICTED and UNQUIESCE clauses to put the database in and take it out of the quiesced state. This state enables database administrators to perform administrative operations that cannot be safely performed in the presence of concurrent transactions, queries, or PL/SQL operations.


Note:

The QUIESCE RESTRICTED clause is valid only if the database resource manager feature is installed and only if the resource manager has been on continuously since database startup in any instances that have opened the database.

If multiple QUIESCE RESTRICTED or UNQUIESCE statements issue at the same time from different sessions or instances, then all but one will receive an error.


QUIESCE RESTRICTED

Specify QUIESCE RESTRICTED to put the database in the quiesced state. For all instances with the database open, this clause has the following effect:

During the quiesced state, you cannot change the Resource Manager plan in any instance.


UNQUIESCE

Specify UNQUIESCE to take the database out of quiesced state. Doing so permits transactions, queries, fetches, and PL/SQL procedures that were initiated by users other than SYS or SYSTEM to be undertaken once again. The UNQUIESCE statement does not have to originate in the same session that issued the QUIESCE RESTRICTED statement.


shutdown_dispatcher_clause

The SHUTDOWN clause is relevant only if your system is using the shared server architecture of Oracle Database. It shuts down a dispatcher identified by dispatcher_name.


Note:

Do not confuse this clause with the SQL*Plus command SHUTDOWN, which is used to shut down the entire database.

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.


REGISTER Clause

Specify REGISTER to instruct the PMON background process to register the instance with the listeners immediately. If you do not specify this clause, then registration of the instance does not occur until the next time PMON executes the discovery routine. As a result, clients may not be able to access the services for as long as 60 seconds after the listener is started.


See Also:

Oracle Database Concepts and Oracle Net Services Administrator's Guide for information on the PMON background process and listeners


alter_system_set_clause

The alter_system_set_clause lets you set or reset the value of any initialization parameter. The parameters are described in "Initialization Parameters and ALTER SYSTEM".

The ability to change initialization parameter values depends on whether you have started up the database with a traditional client-side initialization parameter file (pfile) or with a server parameter file (spfile). To determine whether you can change the value of a particular parameter, query the ISSYS_MODIFIABLE column of the V$PARAMETER dynamic performance view.

When setting a parameter value, you can specify additional settings as follows:


COMMENT

The COMMENT clause lets you associate a comment string with this change in the value of the parameter. If you also specify SPFILE, then this comment will appear in the parameter file to indicate the most recent change made to this parameter.


DEFERRED

The DEFERRED keyword sets or modifies the value of the parameter for future sessions that connect to the database. Current sessions retain the old value.

You must specify DEFERRED if the value of the ISSYS_MODIFIABLE column of V$PARAMETER for this parameter is DEFERRED. If the value of that column is IMMEDIATE, then the DEFERRED keyword in this clause is optional. If the value of that column is FALSE, then you cannot specify DEFERRED in this ALTER SYSTEM statement.


See Also:

Oracle Database Reference for information on the V$PARAMETER dynamic performance view


SCOPE

The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a client-side parameter file (pfile) or server parameter file (spfile).


MEMORY

MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.


SPFILE

SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.


BOTH

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.


SID

The SID clause is relevant only in a Real Application Clusters environment. This clause lets you specify the SID of the instance where the value will take effect.

If you do not specify this clause:

If you specify an instance other than the current instance, then Oracle Database sends a message to that instance to change the parameter value in the memory of that instance.


See Also:

Oracle Database Reference for information about the V$PARAMETER view


alter_system_reset_clause

The alter_system_reset_clause is for use in a Real Application Clusters environment. It gives you separate control for an individual instance over parameters that may have been set for all instances in a server parameter file. The SCOPE clause has the same behavior as described for the alter_system_set_clause.


SID

Specify the SID clause to remove a previously specified setting of this parameter for your instance—that is, a previous ALTER SYSTEM SET ... SID = 'sid' statement. (In a non-RAC environment, you can specify SID = '*' for this clause.) Your instance will assume the value of the parameter as specified in a previous or subsequent ALTER SYSTEM SET ... SID = '*' statement.


See Also:

Real Application Clusters Deployment and Performance for information on setting parameter values for an individual instance in a Real Application Clusters environment


Initialization Parameters and ALTER SYSTEM

You can change the value of many initialization parameters for the current instance, whether you have started the database with a traditional client-side parameter file (pfile) or with a server parameter file (spfile). Oracle Database Reference indicates these parameters in the "Modifiable" category of each parameter description. If you are using a pfile, then the change will persist only for the duration of the instance. However, if you have started the database with an spfile, then you can change the value of the parameter in the spfile itself, so that the new value will occur in subsequent instances.

Three lists follow:

For a description of these parameters, please refer to Oracle Database Reference.


Basic Parameters

CLUSTER_DATABASE
COMPATIBLE
CONTROL_FILES
DB_BLOCK_SIZE
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_DOMAIN
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_UNIQUE_NAME
INSTANCE_NUMBER
JOB_QUEUE_PROCESSES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
NLS_LANGUAGE
NLS_TERRITORY
OPEN_CURSORS
PGA_AGGREGATE_TARGET
PROCESSES
REMOTE_LISTENER
REMOTE_LOGIN_PASSWORDFILE
ROLLBACK_SEGMENTS
SESSIONS
SGA_TARGET
SHARED_SERVERS
STAR_TRANSFORMATION_ENABLED
UNDO_MANAGEMENT
UNDO_TABLESPACE
Parameters by Functional Category

All Parameters in Alphabetical Order

ACTIVE_INSTANCE_COUNT
AQ_TM_PROCESSES
ARCHIVE_LAG_TARGET
AUDIT_FILE_DEST
AUDIT_SYS_OPERATIONS
AUDIT_TRAIL
BACKGROUND_CORE_DUMP
BACKGROUND_DUMP_DEST
BACKUP_TAPE_IO_SLAVES
BITMAP_MERGE_AREA_SIZE
BLANK_TRIMMING
CIRCUITS
CLUSTER_DATABASE
CLUSTER_DATABASE_INSTANCES
CLUSTER_INTERCONNECTS
COMMIT_POINT_STRENGTH
COMPATIBLE
CONTROL_FILE_RECORD_KEEP_TIME
CONTROL_FILES
CORE_DUMP_DEST
CPU_COUNT
CREATE_BITMAP_AREA_SIZE
CREATE_STORED_OUTLINES
CURSOR_SHARING
CURSOR_SPACE_FOR_TIME
DB_nK_CACHE_SIZE
DB_BLOCK_CHECKING
DB_BLOCK_CHECKSUM
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_DOMAIN
DB_FLASHBACK_RETENTION_TARGET
DB_FILE_MULTIBLOCK_READ_COUNT
DB_FILE_NAME_CONVERT
DB_FILES
DB_KEEP_CACHE_SIZE
DB_NAME
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
DB_RECYCLE_CACHE_SIZE
DB_UNIQUE_NAME
DB_WRITER_PROCESSES
DBWR_IO_SLAVES
DDL_WAIT_FOR_LOCKS
DG_BROKER_CONFIG_FILEn
DG_BROKER_START
DISK_ASYNCH_IO
DISPATCHERS
DISTRIBUTED_LOCK_TIMEOUT
DML_LOCKS
ENQUEUE_RESOURCES
EVENT
FAL_CLIENT
FAL_SERVER
FAST_START_MTTR_TARGET
FAST_START_PARALLEL_ROLLBACK
FILE_MAPPING
FILEIO_NETWORK_ADAPTERS
FILESYSTEMIO_OPTIONS
FIXED_DATE
GC_FILES_TO_LOCKS
GCS_SERVER_PROCESSES
GLOBAL_NAMES
HASH_AREA_SIZE
HI_SHARED_MEMORY_ADDRESS
HS_AUTOREGISTER
IFILE
INSTANCE_GROUPS
INSTANCE_NAME
INSTANCE_NUMBER
INSTANCE_TYPE
JAVA_MAX_SESSIONSPACE_SIZE
JAVA_POOL_SIZE
JAVA_SOFT_SESSIONSPACE_LIMIT
JOB_QUEUE_PROCESSES
LARGE_POOL_SIZE
LDAP_DIRECTORY_ACCESS
LICENSE_MAX_SESSIONS
LICENSE_MAX_USERS
LICENSE_SESSIONS_WARNING
LOCAL_LISTENER
LOCK_SGA
LOG_ARCHIVE_CONFIG
LOG_ARCHIVE_DEST
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
LOG_ARCHIVE_DUPLEX_DEST
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_LOCAL_FIRST
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_BUFFER
LOG_CHECKPOINT_INTERVAL
LOG_CHECKPOINT_TIMEOUT
LOG_CHECKPOINTS_TO_ALERT
LOG_FILE_NAME_CONVERT
LOGMNR_MAX_PERSISTENT_SESSIONS
MAX_COMMIT_PROPAGATION_DELAY
MAX_DISPATCHERS
MAX_DUMP_FILE_SIZE
MAX_SHARED_SERVERS
NLS_CALENDAR
NLS_COMP
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
O7_DICTIONARY_ACCESSIBILITY
OBJECT_CACHE_MAX_SIZE_PERCENT
OBJECT_CACHE_OPTIMAL_SIZE
OLAP_PAGE_POOL_SIZE
OPEN_CURSORS
OPEN_LINKS
OPEN_LINKS_PER_INSTANCE
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
OS_AUTHENT_PREFIX
OS_ROLES
OSM_DISKGROUPS
OSM_DISKSTRING
OSM_POWER_LIMIT
PARALLEL_ADAPTIVE_MULTI_USER
PARALLEL_EXECUTION_MESSAGE_SIZE
PARALLEL_INSTANCE_GROUP
PARALLEL_MAX_SERVERS
PARALLEL_MIN_PERCENT
PARALLEL_MIN_SERVERS
PARALLEL_THREADS_PER_CPU
PGA_AGGREGATE_TARGET
PLSQL_CODE_TYPE
PLSQL_DEBUG
PLSQL_NATIVE_LIBRARY_DIR
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
PLSQL_OPTIMIZE_LEVEL
PLSQL_V2_COMPATIBILITY
PLSQL_WARNINGS
PRE_PAGE_SGA
PROCESSES
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
RDBMS_SERVER_DN
READ_ONLY_OPEN_DELAYED
RECOVERY_PARALLELISM
REMOTE_ARCHIVE_ENABLE
REMOTE_DEPENDENCIES_MODE
REMOTE_LISTENER
REMOTE_LOGIN_PASSWORDFILE
REMOTE_OS_AUTHENT
REMOTE_OS_ROLES
REPLICATION_DEPENDENCY_TRACKING
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
RESUMABLE_TIMEOUT
ROLLBACK_SEGMENTS
SERIAL_REUSE
SERVICE_NAMES
SESSION_CACHED_CURSORS
SESSION_MAX_OPEN_FILES
SESSIONS
SGA_MAX_SIZE
SGA_TARGET
SHADOW_CORE_DUMP
SHARED_MEMORY_ADDRESS
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_SIZE
SHARED_SERVER_SESSIONS
SHARED_SERVERS
SKIP_UNUSABLE_INDEXES
SMTP_OUT_SERVER
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
SPFILE
SQL_TRACE
SQL92_SECURITY
SQLTUNE_CATEGORY
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT
STAR_TRANSFORMATION_ENABLED
STATISTICS_LEVEL
STREAMS_POOL_SIZE
TAPE_ASYNCH_IO
THREAD
TIMED_OS_STATISTICS
TIMED_STATISTICS
TRACE_ENABLED
TRACEFILE_IDENTIFIER
TRANSACTIONS
TRANSACTIONS_PER_ROLLBACK_SEGMENT
UNDO_MANAGEMENT
UNDO_RETENTION
UNDO_TABLESPACE
USER_DUMP_DEST
USE_INDIRECT_DATA_BUFFERS
UTL_FILE_DIR
WORKAREA_SIZE_POLICY

System Parameters and ALTER SYSTEM

This section describes system parameters. These parameters are not initialization parameters, so you cannot set them in a pfile or spfile. However, you can set them using an ALTER SYSTEM statement.


USE_STORED_OUTLINES

Syntax:
USE_STORED_OUTLINES = { TRUE | FALSE | category_name }

The USE_STORED_OUTLINES parameter determines whether the optimizer will use stored public outlines to generate execution plans. USE_STORED_OUTLINES is not an initialization parameter.

Shared Server Parameters

When you start your instance, Oracle Database creates shared server processes and dispatcher processes for the shared server architecture based on the values of the SHARED_SERVERS and DISPATCHERS initialization parameters. You can also set the SHARED_SERVERS and DISPATCHERS parameters with ALTER SYSTEM to perform one of the following operations while the instance is running:

Examples


Archiving Redo Logs Manually: Examples

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$]'; 

Enabling Query Rewrite: Example

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

ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE;

Restricting Sessions: Example

You might want to restrict sessions if you are performing application maintenance and you want only application developers with RESTRICTED SESSION system privilege to log on. To restrict sessions, 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; 

Clearing the 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;

Forcing a Checkpoint: Example

The following statement forces a checkpoint:

ALTER SYSTEM CHECKPOINT; 

Enabling Resource Limits: Example

This ALTER SYSTEM statement dynamically enables resource limits:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE; 

Changing Shared Server Settings: Examples

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

ALTER SYSTEM SET SHARED_SERVERS = 25; 

If there are currently fewer than 25 shared server processes, then Oracle Database creates more. If there are currently more than 25, then Oracle Database 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 ipc protocol to 10:

ALTER SYSTEM 
   SET DISPATCHERS = 
      '(INDEX=0)(PROTOCOL=TCP)(DISPATCHERS=5)',
      '(INDEX=1)(PROTOCOL=ipc)(DISPATCHERS=10)'; 

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

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

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


Changing Licensing Parameters: 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, then Oracle Database 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, then 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 this statement, Oracle Database 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 preceding statement, Oracle Database prevents the number of users in the database from exceeding 200.

ALTER SYSTEM SET LICENSE_MAX_USERS = 200; 

Forcing a Log Switch: Example

You might 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 Database is writing to it. The forced log switch affects only the redo log thread of your instance. The following statement forces a log switch:

ALTER SYSTEM SWITCH LOGFILE; 

Enabling Distributed Recovery: Example

The following statement enables distributed recovery:

ALTER SYSTEM ENABLE DISTRIBUTED RECOVERY;

You might 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 is complete, you can then enable distributed recovery again by issuing an ALTER SYSTEM statement with the ENABLE DISTRIBUTED RECOVERY clause.


Terminating a Session: Example

You might want to terminate 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 terminated. 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 terminates the session of the user scott using the SID and SERIAL# values from V$SESSION:

ALTER SYSTEM KILL SESSION '13, 8';

Disconnecting a 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;