[Laskey99] Section 13.2.19 Alter System

ALTER SYSTEM
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']
Keywords
SET
Indicates that one or moresystem-level keywords will follow.
ALLOW_PARTIAL_SN_RESULTS
Allows you to override the INIT.ORA parameterALLOW_PARTIAL_SN_RESULTS for the current instance. A value of TRUEallows results to be retrieved in the GV$ views even if a slaveprocess cannot be created for one or more instances.
BACKUP_DISK_IO_SLAVES
Allows you to override the INIT.ORA parameterBACKUP_DISK_IO_SLAVES for the current instance. Specifies the numberof I/O slaves used to back up, copy, or restore database files todisk.
BACKUP_TAPE_IO_SLAVES
Allows you to override the INIT.ORA parameterBACKUP_TAPE_IO_SLAVES for the current instance. Specifies the numberof I/O slaves used to back up, copy, or restore database files totape.
CACHE_INSTANCES
Specifies the number of instances in a Parallel Server environmentthat will cache a table. Note that this parameter is expected to beobsolete in a future release of Oracle.
CONTROL_FILE_RECORD_KEEP_TIME
Allows you to override the INIT.ORA parameterCONTROL_FILE_RECORD_KEEP_TIME for the current instance. Specifies theminimum number of days a reusable entry is stored in the controlfile. If necessary, the control file will expand to include enoughrecords.
DB_BLOCK_CHECKPOINT_BATCH
Allows you to override the INIT.ORA parameterDB_BLOCK_CHECKPOINT_BATCH for this instance. Specifies a number ofadditional database buffer blocks that can be written out by DBWRwhen it writes buffers to disk, advancing checksum processing.
DB_BLOCK_CHECKSUM
Allows you to override the INIT.ORA parameterDB_BLOCK_CHECKSUM for the current instance. Specifies whether DBWR isto calculate and record a checksum with every database buffer blockwritten. The checksum is then calculated and compared on reads. Thedefault is FALSE.
DB_BLOCK_MAX_DIRTY_TARGET
Allows you to override the INIT.ORA parameterDB_BLOCK_MAX_DIRTY_READ_COPY for this instance. Specifies a targetmaximum number of dirty blocks. When this value is reached, DBWRstarts writing additional blocks in an attempt to bring the number ofdirty blocks down.
DB_FILE_MULTIBLOCK_READ_COUNT
Allows you to override the INIT.ORA parameterDB_FILE_MULTIBLOCK_READ_COUNT for the current instance. Thisspecifies the target number of database block buffers to read in at atime during sequential scans of a table.
FIXED_DATE
Allows you to override the INIT.ORA parameterFIXED_DATE for this instance. This provides a fixed value that isalways returned by SYSDATE for testing.
FREEZE_DB_FOR_FAST_INSTANCE_RECOVERY
Allows you to override the INIT.ORA parameterFREEZE_DB_FOR_FAST_INSTANCE_RECOVERY for this database. A value ofTRUE freezes the entire database during an instance recovery. Thiscauses the database to appear to hang for current users, but makesthe instance recovery complete faster. FALSE does not freeze thedatabase, and DEFERRED defers it.
GC_DEFER_TIME
Allows you to override the INIT.ORA parameterGC_DEFER_TIME for this instance. Specifies how many hundredths of asecond the instance will wait or defer before writing out bufferswhen a forced write is signaled from another instance.
GLOBAL_NAMES
Specifies whether global naming will be enforced (TRUE) or notenforced (FALSE).
HASH_MULTIBLOCK_IO_COUNT
Specifies the number of database blocks to be read or written duringhash I/O operations.
JOB_QUEUE_PROCESSES
Allows you to override the INIT.ORA parameterJOB_QUEUE_PROCESSES for this instance. Specifies the maximum numberof SNP background processes. The allowed values are betweenand 36.
LICENSE_MAX_SESSIONS
Specifies the maximum number of sessions permitted on this instance.A value ofindicates that there is no limit.
LICENSE_MAX_USERS
Specifies the maximum number of users in this database. A value ofindicates that there is no limit.
LICENSE_SESSIONS_WARNING
Specifies the maximum number of sessions permitted on this instancebefore a warning message is written to the alert file. A value ofindicates that there is no limit.
LOG_ARCHIVE_DUPLEX_DEST
Overrides the INIT.ORA parameterLOG_ARCHIVE_DUPLEX_DEST for this instance. Specifies an additionalarchive log destination that is to be written to when redo logs arearchived.
LOG_ARCHIVE_MIN_SUCCEED_DEST
Overrides the INIT.ORA parameterLOG_ARCHIVE_MIN_SUCCEED_DEST for this instance. Specifies the minimumnumber of archive file writes that have to succeed before the archiveis considered complete.
LOG_CHECKPOINT_INTERVAL
Overrides the INIT.ORA parameterLOG_CHECKPOINT_INTERVAL for this instance. Specifies the number ofredo log blocks that can be written before a checkpoint is forced.
LOG_CHECKPOINT_TIMEOUT
Overrides the INIT.ORA parameterLOG_CHECKPOINT_TIMEOUT for this instance. Specifies in seconds themaximum amount of time between checkpoints. This can be used to forcea checkpoint on a periodic basis even when there is minimal databaseactivity.
LOG_SMALL_ENTRY_MAX_SIZE
Overrides the INIT.ORA parameterLOG_SMALL_ENTRY_MAX_SIZE for this instance. Specifies the largestcopy of log buffers that can be performed using the log allocationlatch without resorting to using the log buffer copy latch.
MAX_DUMP_FILE_SIZE
Allows you to override the INIT.ORA parameterMAX_DUMP_FILE_SIZE for the current instance. The integer specified isthe maximum number of operating system blocks to be used for a tracefile. 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 notenforced (FALSE).
OBJECT_CACHE_MAX_SIZE_PERCENT
Allows you to override the INIT.ORA parameterOBJECT_CACHE_MAX_SIZE_PERCENT for this instance. This value is thepercent by which the object cache can exceed the optimal size. Whenthis value is exceeded, the cache will be reduced to the optimalsize.
OBJECT_CACHE_OPTIMAL_SIZE
Allows you to override the INIT.ORA parameterOBJECT_CACHE_OPTIMAL_SIZE for this instance. This specifies the sizeto which the object cache is reduced when it exceeds its maximumvalue.
OPS_ADMIN_GROUP
Allows you to override the INIT.ORA parameterOPS_ADMIN_GROUP for this instance. In a Parallel Server environment,the instances can be divided into one or more groups to easeadministration. The effect of this parameter is to limit theinstances reflected in the GV$ views.
PARALLEL_INSTANCE_GROUP
Allows you to override the INIT.ORA parameterPARALLEL_INSTANCE_GROUP for this instance. This specifies theinstance groups to which parallel query slaves can be spawned.
PARALLEL_TRANSACTION_RESOURCE_TIMEOUT
Allows you to override the INIT.ORA parameterPARALLEL_TRANSACTION_RESOURCE_TIMEOUT for this instance. ThisParallel Server parameter specifies how many seconds a session willwait on an object locked by another session.
PLSQL_V2_COMPATIBILITY
Specifies whether to support, for compatibility reasons, PL/SQL V2constructs that are no longer supported in Oracle8 PL/SQL V3.
REMOTE_DEPENDENCIES_MODE
Allows you to override the INIT.ORA parameterREMOTE_DEPENDENCIES_MODE for this session. This specifies how PL/SQLwill treat dependencies upon remote procedures:
TIMESTAMP
Specifies thattimestamps are to be checked, and will execute the local procedure ifthe timestamp of the remote procedure matches. This is the default.
SIGNATURE
Specifies that thelocal procedure can continue to call the remote procedure if thesignature is considered safe.
RESOURCE_LIMIT
Allows you to override the INIT.ORA parameterRESOURCE_LIMIT for this instance. A value of TRUE enables enforcementof resource limits as specified in user profiles.
SCAN_INSTANCES
Specifies how many instances are to participate in parallelizedoperations. This parameter will become obsolete in the next majorrelease of Oracle.
SORT_AREA_SIZE
Allows you to override the INIT.ORA parameterSORT_AREA_SIZE for this session. This specifies the amount of memoryallocated out of the PGA (the SGA in an MTS environment) for sorts.
SORT_AREA_RETAINED_SIZE
Allows you to override the INIT.ORA parameterSORT_AREA_RETAINED_SIZE for this session. This is the maximum sizethat the sort area in the PGA retains between the sort completing andfetching the last row from the sort area.
SORT_DIRECT_WRITES
Allows you to override the INIT.ORA parameterSORT_DIRECT_WRITES for this session. The valid values are:
AUTO
Oracle will bypass the buffer poolunder certain circumstances. This is the default.
TRUE
Oracle will bypass the buffer pool andwrite temporary segments directly to disk.
FALSE
Oracle will perform all reads andwrites to the temporary segments through the buffer cache.
SORT_READ_FAC
Allows you to override the INIT.ORA parameterSORT_READ_FAC for this instance. This parameter provides Oracle witha ratio indicating relative disk performance. The default isoperating system dependent.
SORT_WRITE_BUFFERS
Allows you to override the INIT.ORA parameterSORT_WRITE_BUFFERS for this instance. This parameter specifies thenumber of sort buffers to use when SORT_DIRECT_WRITES is set to TRUE.
SORT_WRITE_BUFFER_SIZE
Allows you to override the INIT.ORA parameterSORT_WRITE_BUFFER_SIZE for this instance. This parameter specifiesthe size of the sort write buffer to use when SORT_DIRECT_WRITES isset to TRUE.
SPIN_COUNT
Allows you to override the INIT.ORA parameterSPIN_COUNT for this instance. This parameter specifies the number oftimes a process will wait on a latch before sleeping.
TEXT_ENABLED
Allows you to override the INIT.ORA parameterTEXT_ENABLED for this instance. This parameter determines whether toenable the CONTAINS clause in the Oracle ConText option or the OracleConText cartridge.
TIMED_STATISTICS
Allows you to override the INIT.ORA parameterTIMED_STATISTICS for this instance. When this parameter is set toTRUE, Oracle will call system services to determine the length oftime required for most operations.
TIMED_OS_STATISTICS
Allows you to override the INIT.ORA parameterTIMED_OS_STATISTICS for this instance. Specifies the number ofseconds between calls to collect operating system statistics.
TRANSACTION_AUDITING
Allows you to override the INIT.ORA parameterTRANSACTION_AUDITING for the current instance. A value of TRUE causesOracle to write information into the redo log to identify the userand instance responsible for individual modifications to thedatabase. FALSE causes Oracle not to write information, and DEFERREDdefers the operation.
USER_DUMP_DEST
Allows you to override the INIT.ORA parameterUSER_DUMP_DEST for the current instance. Specifies a directory inwhich trace files are written.
ARCHIVE_LOG
Manually archives redo log file groups; enables or disables automaticarchiving as follows:
THREAD
Specifies the thread containingthe redo log file group to be archived. This parameter is onlyrequired when running the Parallel Server option in parallel mode.
START
Enables automatic archiving of redo loggroups.
STOP
Disables automatic archiving of redo loggroups.
SEQ
Specifies the log sequence numberof the redo log file group to be manually archived. The database mustbe mounted but may be open or closed to issue this command.
CHANGE
Manually archives the online redo logfile group containing the redo log entry with the system changenumber (SCN) specified by integer. If the SCN isthe current log group, a log switch is performed. The database mustbe open to use this parameter.
CURRENT
Manually forces a log switch andarchives the current redo log file group. The database must be opento use this parameter.
GROUP
Manually archives the online redolog file group with the specified GROUP value, which can be found inDBA_LOG_FILES. The database must be mounted but may be open or closedto issue this command.
LOGFILE
Manually archives the online redo logfile group containing the log file member identified byfilename. The database must be mounted but maybe open or closed to issue this command.
NEXT
Manually archives the next onlineredo log file group that is full but has not yet been archived. Thedatabase must be mounted but may be open or closed to issue thiscommand.
ALL
Manually archives all online redolog file groups that are full but have not been archived. Thedatabase must be mounted but may be open or closed to issue thiscommand.
CHECKPOINT
Causes Oracle to perform a checkpoint. The database may be open orclosed to issue this command. You may specify:
GLOBAL
Performs a checkpoint for allinstances that have opened the database.
LOCAL
Performs a checkpoint only forthis instance.
CHECK DATAFILES
Verifies access to online datafiles. The database may be open orclosed to issue this command. You may specify:
GLOBAL
Verifies that all instances thathave opened the database can access the datafiles.
LOCAL
Verifies that thisinstance 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 forapplication failover (if configured) to another instance in aParallel Server environment.
ENABLE DISTRIBUTED RECOVERY
Specifies that distributed recovery is to be enabled and—in asingle-process environment—used to initiate distributedrecovery.
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 tothe instance.
DISABLE RESTRICTED SESSION
Allows any user with the CREATE SESSION privilege to log on to theinstance.
FLUSH SHARED_POOL
Clears all data from the instance shared pool. The database may bedismounted 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. Inaddition, the ARCHIVE LOG command requires that you have the OSDBA orOSOPER role enabled. Except as noted, the database may be mounted andopen to issue these commands.
Examples
The following example changes the number of dispatcher processes forTCP 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, andsets 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 thespecified location:
ALTER SYSTEM
ARCHIVE LOG SEQ 123 TO '/disk09/oracle/archive'

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint
Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next
Related Content

ALTER TABLE
From: Managing and Using MySQL, 2nd Edition
Altering Databases
From: MySQL™: The definitive guide to using, programming, and administering MySQL 4.1 and 5.0, Third Edition
ALTER DATABASE
From: MySQL™: The definitive guide to using, programming, and administering MySQL 4.1 and 5.0, Third Edition
ALTER TABLE
From: MySQL™: The definitive guide to using, programming, and administering MySQL 4.1 and 5.0, Third Edition
Altering a Database
From: Sams Teach Yourself MySQL® in 10 Minutes
Altering Tables
From: Sams Teach Yourself MySQL® in 10 Minutes
Create and alter databases
From: MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, Exam 70-228, Second Edition
Altering tables
From: Using IBM DB2 for i as a Storage Engine of MySQL
ALTER SYSTEM
From: Oracle SQL: The Essential Reference
CREATE/ALTER DATABASE
From: Oracle SQL: The Essential Reference