ALTER SYSTEM

Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Name

ALTER SYSTEM

Synopsis

ALTER SYSTEM
   [SET
     {
      [ALLOW_PARTIAL_SN_RESULTS = {TRUE | FALSE} ]
[BACKUP_DISK_IO_SLAVES = integer DEFERRED]
[BACKUP_TAPE_IO_SLAVES = integer DEFERRED]
      [CACHE_INSTANCES = integer]
[CONTROL_FILE_RECORD_KEEP_TIME = integer
DEFERRED]
      [DB_BLOCK_CHECKPOINT_BATCH = integer]
      [DB_BLOCK_CHECKSUM = {TRUE | FALSE} ]
      [DB_BLOCK_MAX_DIRTY_TARGET = integer]
[DB_FILE_MULTIBLOCK_READ_COUNT = integer]
      [FIXED_DATE = {'DD_MM_YY’ | ‘YYYY_MM_DD_HH24_MI_SS'} ]
[FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY = {TRUE | FALSE } DEFERRED]
      [GC_DEFER_TIME = integer]
      [GLOBAL_NAMES = {TRUE | FALSE} ]
      [HASH_MULTIBLOCK_IO_COUNT = integer]
      [JOB_QUEUE_PROCESSES = integer]
      [LICENSE_MAX_SESSIONS = integer]
      [LICENSE_MAX_USERS = integer]
      [LICENSE_SESSIONS_WARNING = integer]
      [LOG_ARCHIVE_DUPLEX_DEST = 'text' ]
[LOG_ARCHIVE_MIN_SUCCEED_DEST = integer]
      [LOG_CHECKPOINT_INTERVAL = integer]
      [LOG_CHECKPOINT_TIMEOUT = integer]
      [LOG_SMALL_ENTRY_MAX_SIZE = integer]
[MAX_DUMP_FILE_SIZE = {size | UNLIMITED}]
[MTS_DISPATCHERS = 'protocol,
integer']
      [MTS_SERVERS = integer]
      [RESOURCE_LIMIT   {TRUE | FALSE} ]
[OBJECT_CACHE_MAX_SIZE_PERCENT = integer]
      [OBJECT_CACHE_OPTIMAL_SIZE = integer]
      [OPS_ADMIN_GROUP = 'text' ]
      [PARALLEL_INSTANCE_GROUP = 'text' ]
[PARALLEL_TRANSACTION_RESOURCE_TIMEOUT =
integer]
      [PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} ]
      [REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE} ]
      [RESOURCE_LIMIT = {TRUE | FALSE} ]
      [SCAN_INSTANCES = integer]
      [SORT_AREA_SIZE = integer]
      [SORT_AREA_RETAINED_SIZE = integer]
      [SORT_DIRECT_WRITES = {AUTO | TRUE | FALSE} ]
      [SORT_READ_FAC = integer]
      [SORT_WRITE_BUFFERS = integer]
      [SORT_WRITE_BUFFER_SIZE = integer]
      [SPIN_COUNT = integer]
      [TEXT_ENABLED = {TRUE | FALSE} ]
      [TIMED_STATISTICS = {TRUE | FALSE} ]
      [TIMED_OS_STATISTICS = integer]
      [TRANSACTION_AUDITING = {TRUE | FALSE} DEFERRED]
      [USER_DUMP_DEST = 'dirname']
      }
   [ARCHIVE_LOG [THREAD integer]
      {
      [START [TO 'destination'] ]
      [STOP]
[SEQ integer [TO
'destination'] ]
[CHANGE integer [TO
'destination'] ]
      [CURRENT [TO 'destination'] ]
[GROUP integer [TO
'destination'] ]
[LOGFILE 'filename' [TO
'destination'] ]
      [NEXT [TO 'destination'] ]
      [ALL [TO 'destination'] ]
      }
   [CHECKPOINT {GLOBAL | LOCAL} ]
   [CHECK DATAFILES {GLOBAL | LOCAL} ]
[DISCONNECT SESSION
'sid_integer.session_integer'
POST_TRANSACTION]
   [ENABLE DISTRIBUTED RECOVERY]
   [DISABLE DISTRIBUTED RECOVERY]
   [ENABLE RESTRICTED SESSION]
   [DISABLE RESTRICTED SESSION]
   [FLUSH SHARED_POOL]
   [SWITCH LOGFILE]
[KILL SESSION 'sid_integer,
session_integer']

Makes dynamic changes to the database instance.

Keywords

SET

Indicates that one or more system-level keywords will follow.

ALLOW_PARTIAL_SN_RESULTS

Allows you to override the INIT.ORA parameter ALLOW_PARTIAL_SN_RESULTS for the current instance. A value of TRUE allows results to be retrieved in the GV$ views even if a slave process cannot be created for one or more instances.

BACKUP_DISK_IO_SLAVES

Allows you to override the INIT.ORA parameter BACKUP_DISK_IO_SLAVES for the current instance. Specifies the number of I/O slaves used to back up, copy, or restore database files to disk.

BACKUP_TAPE_IO_SLAVES

Allows you to override the INIT.ORA parameter BACKUP_TAPE_IO_SLAVES for the current instance. Specifies the number of I/O slaves used to back up, copy, or restore database files to tape.

CACHE_INSTANCES

Specifies the number of instances in a Parallel Server environment that will cache a table. Note that this parameter is expected to be obsolete in a future release of Oracle.

CONTROL_FILE_RECORD_KEEP_TIME

Allows you to override the INIT.ORA parameter CONTROL_FILE_RECORD_KEEP_TIME for the current instance. Specifies the minimum number of days a reusable entry is stored in the control file. If necessary, the control file will expand to include enough records.

DB_BLOCK_CHECKPOINT_BATCH

Allows you to override the INIT.ORA parameter DB_BLOCK_CHECKPOINT_BATCH for this instance. Specifies a number of additional database buffer blocks that can be written out by DBWR when it writes buffers to disk, advancing checksum processing.

DB_BLOCK_CHECKSUM

Allows you to override the INIT.ORA parameter DB_BLOCK_CHECKSUM for the current instance. Specifies whether DBWR is to calculate and record a checksum with every database buffer block written. The checksum is then calculated and compared on reads. The default is FALSE.

DB_BLOCK_MAX_DIRTY_TARGET

Allows you to override the INIT.ORA parameter DB_BLOCK_MAX_DIRTY_READ_COPY for this instance. Specifies a target maximum number of dirty blocks. When this value is reached, DBWR starts writing additional blocks in an attempt to bring the number of dirty blocks down.

DB_FILE_MULTIBLOCK_READ_COUNT

Allows you to override the INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT for the current instance. This specifies the target number of database block buffers to read in at a time during sequential scans of a table.

FIXED_DATE

Allows you to override the INIT.ORA parameter FIXED_DATE for this instance. This provides a fixed value that is always returned by SYSDATE for testing.

FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY

Allows you to override the INIT.ORA parameter FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY for this database. A value of TRUE freezes the entire database during an instance recovery. This causes the database to appear to hang for current users, but makes the instance recovery complete faster. FALSE does not freeze the database, and DEFERRED defers it.

GC_DEFER_TIME

Allows you to override the INIT.ORA parameter GC_DEFER_TIME for this instance. Specifies how many hundredths of a second the instance will wait or defer before writing out buffers when a forced write is signaled from another instance.

GLOBAL_NAMES

Specifies whether global naming will be enforced (TRUE) or not enforced (FALSE).

HASH_MULTIBLOCK_IO_COUNT

Specifies the number of database blocks to be read or written during hash I/O operations.

JOB_QUEUE_PROCESSES

Allows you to override the INIT.ORA parameter JOB_QUEUE_PROCESSES for this instance. Specifies the maximum number of SNP background processes. The allowed values are between and 36.

LICENSE_MAX_SESSIONS

Specifies the maximum number of sessions permitted on this instance. A value of indicates that there is no limit.

LICENSE_MAX_USERS

Specifies the maximum number of users in this database. A value of indicates that there is no limit.

LICENSE_SESSIONS_WARNING

Specifies the maximum number of sessions permitted on this instance before a warning message is written to the alert file. A value of indicates that there is no limit.

LOG_ARCHIVE_DUPLEX_DEST

Overrides the INIT.ORA parameter LOG_ARCHIVE_DUPLEX_DEST for this instance. Specifies an additional archive log destination that is to be written to when redo logs are archived.

LOG_ARCHIVE_MIN_SUCCEED_DEST

Overrides the INIT.ORA parameter LOG_ARCHIVE_MIN_SUCCEED_DEST for this instance. Specifies the minimum number of archive file writes that have to succeed before the archive is considered complete.

LOG_CHECKPOINT_INTERVAL

Overrides the INIT.ORA parameter LOG_CHECKPOINT_INTERVAL for this instance. Specifies the number of redo log blocks that can be written before a checkpoint is forced.

LOG_CHECKPOINT_TIMEOUT

Overrides the INIT.ORA parameter LOG_CHECKPOINT_TIMEOUT for this instance. Specifies in seconds the maximum amount of time between checkpoints. This can be used to force a checkpoint on a periodic basis even when there is minimal database activity.

LOG_SMALL_ENTRY_MAX_SIZE

Overrides the INIT.ORA parameter LOG_SMALL_ENTRY_MAX_SIZE for this instance. Specifies the largest copy of log buffers that can be performed using the log allocation latch without resorting to using the log buffer copy latch.

MAX_DUMP_FILE_SIZE

Allows you to override the INIT.ORA parameter MAX_DUMP_FILE_SIZE for the current instance. The integer specified is the maximum number of operating system blocks to be used for a trace file. A value of UNLIMITED specifies that no limit is to be imposed.

MTS_DISPATCHERS

Changes the number of dispatcher processes for the named protocol. The database must be open to issue this command.

MTS_SERVERS

Changes the minimum number of shared MTS server processes.

RESOURCE_LIMIT

Specifies whether resource limits will be enforced (TRUE) or not enforced (FALSE).

OBJECT_CACHE_MAX_SIZE_PERCENT

Allows you to override the INIT.ORA parameter OBJECT_CACHE_MAX_SIZE_PERCENT for this instance. This value is the percent by which the object cache can exceed the optimal size. When this value is exceeded, the cache will be reduced to the optimal size.

OBJECT_CACHE_OPTIMAL_SIZE

Allows you to override the INIT.ORA parameter OBJECT_CACHE_OPTIMAL_SIZE for this instance. This specifies the size to which the object cache is reduced when it exceeds its maximum value.

OPS_ADMIN_GROUP

Allows you to override the INIT.ORA parameter OPS_ADMIN_GROUP for this instance. In a Parallel Server environment, the instances can be divided into one or more groups to ease administration. The effect of this parameter is to limit the instances reflected in the GV$ views.

PARALLEL_INSTANCE_GROUP

Allows you to override the INIT.ORA parameter PARALLEL_INSTANCE_GROUP for this instance. This specifies the instance groups to which parallel query slaves can be spawned.

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT

Allows you to override the INIT.ORA parameter PARALLEL_TRANSACTION_RESOURCE_TIMEOUT for this instance. This Parallel Server parameter specifies how many seconds a session will wait on an object locked by another session.

PLSQL_V2_COMPATIBILITY

Specifies whether to support, for compatibility reasons, PL/SQL V2 constructs that are no longer supported in Oracle8 PL/SQL V3.

REMOTE_DEPENDENCIES_MODE

Allows you to override the INIT.ORA parameter REMOTE_DEPENDENCIES_MODE for this session. This specifies how PL/SQL will treat dependencies upon remote procedures:

TIMESTAMP: Specifies that timestamps are to be checked, and will execute the local procedure if the timestamp of the remote procedure matches. This is the default.
SIGNATURE: Specifies that the local procedure can continue to call the remote procedure if the signature is considered safe.

RESOURCE_LIMIT

Allows you to override the INIT.ORA parameter RESOURCE_LIMIT for this instance. A value of TRUE enables enforcement of resource limits as specified in user profiles.

SCAN_INSTANCES

Specifies how many instances are to participate in parallelized operations. This parameter will become obsolete in the next major release of Oracle.

SORT_AREA_SIZE

Allows you to override the INIT.ORA parameter SORT_AREA_SIZE for this session. This specifies the amount of memory allocated out of the PGA (the SGA in an MTS environment) for sorts.

SORT_AREA_RETAINED_SIZE

Allows you to override the INIT.ORA parameter SORT_AREA_RETAINED_SIZE for this session. This is the maximum size that the sort area in the PGA retains between the sort completing and fetching the last row from the sort area.

SORT_DIRECT_WRITES

Allows you to override the INIT.ORA parameter SORT_DIRECT_WRITES for this session. The valid values are:

AUTO: Oracle will bypass the buffer pool under certain circumstances. This is the default.
TRUE: Oracle will bypass the buffer pool and write temporary segments directly to disk.
FALSE: Oracle will perform all reads and writes to the temporary segments through the buffer cache.

SORT_READ_FAC

Allows you to override the INIT.ORA parameter SORT_READ_FAC for this instance. This parameter provides Oracle with a ratio indicating relative disk performance. The default is operating system dependent.

SORT_WRITE_BUFFERS

Allows you to override the INIT.ORA parameter SORT_WRITE_BUFFERS for this instance. This parameter specifies the number of sort buffers to use when SORT_DIRECT_WRITES is set to TRUE.

SORT_WRITE_BUFFER_SIZE

Allows you to override the INIT.ORA parameter SORT_WRITE_BUFFER_SIZE for this instance. This parameter specifies the size of the sort write buffer to use when SORT_DIRECT_WRITES is set to TRUE.

SPIN_COUNT

Allows you to override the INIT.ORA parameter SPIN_COUNT for this instance. This parameter specifies the number of times a process will wait on a latch before sleeping.

TEXT_ENABLED

Allows you to override the INIT.ORA parameter TEXT_ENABLED for this instance. This parameter determines whether to enable the CONTAINS clause in the Oracle ConText option or the Oracle ConText cartridge.

TIMED_STATISTICS

Allows you to override the INIT.ORA parameter TIMED_STATISTICS for this instance. When this parameter is set to TRUE, Oracle will call system services to determine the length of time required for most operations.

TIMED_OS_STATISTICS

Allows you to override the INIT.ORA parameter TIMED_OS_STATISTICS for this instance. Specifies the number of seconds between calls to collect operating system statistics.

TRANSACTION_AUDITING

Allows you to override the INIT.ORA parameter TRANSACTION_AUDITING for the current instance. A value of TRUE causes Oracle to write information into the redo log to identify the user and instance responsible for individual modifications to the database. FALSE causes Oracle not to write information, and DEFERRED defers the operation.

USER_DUMP_DEST

Allows you to override the INIT.ORA parameter USER_DUMP_DEST for the current instance. Specifies a directory in which trace files are written.

ARCHIVE_LOG

Manually archives redo log file groups; enables or disables automatic archiving as follows:

THREAD: Specifies the thread containing the redo log file group to be archived. This parameter is only required when running the Parallel Server option in parallel mode.
START: Enables automatic archiving of redo log groups.
STOP: Disables automatic archiving of redo log groups.
SEQ: Specifies the log sequence number of the redo log file group to be manually archived. The database must be mounted but may be open or closed to issue this command.
CHANGE: Manually archives the online redo log file group containing the redo log entry with the system change number (SCN) specified by integer. If the SCN is the current log group, a log switch is performed. The database must be open to use this parameter.
CURRENT: Manually forces a log switch and archives the current redo log file group. The database must be open to use this parameter.
GROUP: Manually archives the online redo log file group with the specified GROUP value, which can be found in DBA_LOG_FILES. The database must be mounted but may be open or closed to issue this command.
LOGFILE: Manually archives the online redo log file group containing the log file member identified by filename. The database must be mounted but may be open or closed to issue this command.
NEXT: Manually archives the next online redo log file group that is full but has not yet been archived. The database must be mounted but may be open or closed to issue this command.
ALL: Manually archives all online redo log file groups that are full but have not been archived. The database must be mounted but may be open or closed to issue this command.

CHECKPOINT

Causes Oracle to perform a checkpoint. The database may be open or closed to issue this command. You may specify:

GLOBAL: Performs a checkpoint for all instances that have opened the database.
LOCAL: Performs a checkpoint only for this instance.

CHECK DATAFILES

Verifies access to online datafiles. The database may be open or closed to issue this command. You may specify:

GLOBAL: Verifies that all instances that have opened the database can access the datafiles.
LOCAL: Verifies that this instance can access the datafiles.

DISCONNECT SESSION

Disconnects a session in the current instance using SID and SESSION# from V$SESSION. The current transaction is allowed to complete, and, since the session is only disconnected, it becomes a candidate for application failover (if configured) to another instance in a Parallel Server environment.

ENABLE DISTRIBUTED RECOVERY

Specifies that distributed recovery is to be enabled and—in a single-process environment—used to initiate distributed recovery.

DISABLE DISTRIBUTED RECOVERY

Specifies that distributed recovery is to be disabled.

ENABLE RESTRICTED SESSION

Allows only users with the RESTRICTED SESSION privilege to log on to the instance.

DISABLE RESTRICTED SESSION

Allows any user with the CREATE SESSION privilege to log on to the instance.

FLUSH SHARED_POOL

Clears all data from the instance shared pool. The database may be dismounted or mounted, open or closed, to issue this command.

SWITCH LOGFILE

Causes Oracle to switch redo log file groups.

KILL SESSION

Terminates a session using SID and SESSION# from V$SESSION.

Notes

You must have the ALTER SYSTEM privilege to issue this command. In addition, the ARCHIVE LOG command requires that you have the OSDBA or OSOPER role enabled. Except as noted, the database may be mounted and open to issue these commands.

Examples

The following example changes the number of dispatcher processes for TCP to 10 and for DECNet to 8:

ALTER SYSTEM
   SET MTS_DISPATCHERS = 'TCP,10'
       MTS_DISPATCHERS = 'DECnet,8'

The following example sets the maximum number of sessions to 100, and sets the warning threshold to 80:

ALTER SYSTEM
   SET LICENSE_MAX_SESSIONS = 100
   LICENSE_SESSIONS_WARNING = 80

The following example archives log sequence number 123 to the specified location:

ALTER SYSTEM 
   ARCHIVE LOG SEQ 123 TO '/disk09/oracle/archive'

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for ALTER SYSTEM

Create new playlist

Sign In

Sign Up

Name

Synopsis

Keywords

Notes

Examples

Table of Contents for
ALTER SYSTEM