ALTER SYSTEM

Modify system settings.

Syntax:

   ALTER SYSTEM ARCHIVE LOG archive_log_clause
   ALTER SYSTEM CHECKPOINT [GLOBAL | LOCAL]
   ALTER SYSTEM CHECK DATAFILES [GLOBAL | LOCAL]
   ALTER SYSTEM FLUSH SHARED POOL
   ALTER SYSTEM SWITCH LOGFILE
   ALTER SYSTEM {ENABLE | DISABLE} DISTRIBUTED RECOVERY
   ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION
   ALTER SYSTEM {SUSPEND | RESUME}
   ALTER SYSTEM SHUTDOWN [IMMEDIATE] dispatcher_name
   ALTER SYSTEM KILL SESSION 'int1, int2' [POST TRANSACTION] [IMMEDIATE]
   ALTER SYSTEM DISCONNECT SESSION 'int1, int2' [IMMEDIATE]
   ALTER SYSTEM SET option(s)

options:
   AQ_TM_PROCESSES = int

   BACKGROUND_DUMP_DEST = 'text'
   BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED

   CONTROL_FILE_RECORD_KEEP_TIME = int
   CORE_DUMP_DEST = 'text'
   CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name' } [NOOVERRIDE]
   CURSOR_SHARING = {force|exact}

   DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED
   DB_BLOCK_CHECKSUM = {TRUE | FALSE}
   DB_BLOCK_MAX_DIRTY_TARGET = int
   DB_FILE_DIRECT_IO_COUNT = int DEFERRED
   DB_FILE_MULTIBLOCK_READ_COUNT = int

   FAST_START_IO_TARGET = int
   FAST_START_PARALLEL_ROLLBACK = {FALSE | LOW | HIGH}
   FIXED_DATE = { 'DD_MM_YY' | 'YYYY_MI_DD_HH24_MI-SS' }

   GC_DEFER_TIME = int
   GLOBAL_NAMES = {TRUE | FALSE}

   HASH_MULTIBLOCK_IO_COUNT = int
   HS_AUTOREGISTER = {TRUE | FALSE}

   JOB_QUEUE_PROCESSES = int

   LICENSE_MAX_SESSIONS = int
   LICENSE_MAX_USERS = int
   LICENSE_SESSIONS_WARNING = int
   LOG_ARCHIVE_DEST = string
   LOG_ARCHIVE_DEST_n = {null_string | 
      {LOCATION=local_pathname | SERVICE=tnsnames_service} 
          [MANDATORY | OPTIONAL] [REOPEN[=integer]]} 
   LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}
   LOG_ARCHIVE_DUPLEX_DEST = string
   LOG_ARCHIVE_MAX_PROCESSES = int
   LOG_ARCHIVE_MIN_SUCCEED_DEST = int
   LOG_ARCHIVE_TRACE = int
   LOG_CHECKPOINT_INTERVAL = int
   LOG_CHECKPOINT_TIMEOUT = int

   MAX_DUMP_FILE_SIZE = { size | 'unlimited'} [DEFERRED]
   MTS_DISPATCHERS = dispatcher_clause
   MTS_SERVERS = int

   OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED 
   OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED
   OPTIMIZER_MAX_PERMUTATIONS = int NOOVERRIDE

   PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE}
   PARALLEL_INSTANCE_GROUP = 'text'
   PARALLEL_THREADS_PER_CPU = int
   PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED]

   QUERY_REWRITE_ENABLED = {TRUE | FALSE} [DEFERRED | NOOVERRIDE]
   QUERY_REWRITE_INTEGRITY = {ENFORCED | TRUSTED | STALE_TOLERATED}

   REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}
   RESOURCE_LIMIT = {TRUE | FALSE}
   RESOURCE_MANAGER_PLAN = plan_name

   SORT_AREA_RETAINED_SIZE = int DEFERRED
   SORT_AREA_SIZE = int DEFERRED
   SORT_MULTIBLOCK_READ_COUNT = int DEFERRED
   STANDBY_ARCHIVE_DEST = string

   TIMED_STATISTICS = {TRUE | FALSE} 
   TIMED_OS_STATISTICS = int
   TRANSACTION_AUDITING = {TRUE | FALSE} DEFERRED

   USE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' } [NOOVERRIDE]
   USER_DUMP_DEST = 'directory_name'

Oracle 7 options now obsolete in v8
   CACHE_INSTANCES = int
   SCAN_INSTANCES = int

"An expert is someone who knows some of the worst mistakes that can be made in his subject and how to avoid them." - Werner Heisenberg

Related Commands:

ALTER DATABASE

ALTER SESSION
STARTUP

Related Views:

 DBA_DATA_FILES
 V$ARCHIVE
 V$ARCHIVED_LOG
 V$ARCHIVE_DEST
 V$ARCHIVE_PROCESSES
 V$SHARED_POOL_RESERVED 
 V$SESSION



Back to the Top

Simon Sheppard
SS64.com