oracle alter system命令使用

我們可以利用alter system語句在數據庫咝羞^程中修改初始化參數的值。
如果instance在啟動時使用的是文本初始化參數文件，那樣我們在利用alter system語句修改的初始化參數僅在當前的instance中有效，
并且所做的修改不會記錄在初始化參數文件中。如果我們要永久的修改初始參數，則需要手工的編輯文本初始化參數文件。
如果instance在啟動時使用的是服務器初始化參數文件(spfile),這樣我們就可以省去手工編輯初始化參數文件的麻煩。
在使用alter system語句時，可以在set子句中通過指定scope的值來設定alter system語句的影響范圍。
所謂影響范圍是指：
1.對當前instance(實例)有效，只記錄在內存中，重啟DB就會消失。
2.永久有效，所做的修改會記錄到服務器初始化參數文件中，重啟DB不會消失。
SCOPE的設定取值有如下三種：
1. scope=spfile: 對參數的修改僅記錄在務器初始化參數文件中，修改后的參數在下次啟動DB時生效。適用于動態和靜態初始化參數。
2. scope=memory: 對參數的修改僅記錄在內存中，對于動態初始化參數的修改立即生效。在重啟DB后會丟失,會復原為修改前的參數值。
3. scope=both:   對參數的修改會同時記錄在服務器參數文件和內存中，對動態參數立即生效，對靜態參數不能用這個選項。
   如果使用了服務器參數文件，則在執行alter system語句時，scope=both是default的選項。
   如果沒有使用服務器參數文件，而在執行alter system語句時指定scope=spfile|both都會出錯。

注意點：
1. 在修改靜態初始化參數時，我們只能指定scope=spfile;
2. 在修改動態初始化參數時，我們可以指定 deferred 關鍵字來對所做的修改延遲到新的session產生時生效。
3. 如果修改的初始化參數是由多個字符串姐成的值，必需用同時設定不需變化部分的值，alter system不支持指定位置的修改。


下面我們來看一個對靜態參數log_buffer的修改的全過程。

Microsoft Windows 2000 [Version 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 3月 12 10:51:36 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn sys/password@ora9i as sysdba
已連線.
SQL> show parameter log_buff

NAME                                 TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
log_buffer                           integer      524288

SQL> alter system set log_buffer =655360 scope=both;
alter system set log_buffer =655360 scope=both
                 *
ERROR 在行 1:
ORA-02095: 無法修改所指定的初使化參數


SQL> alter system set log_buffer =655360 ;
alter system set log_buffer =655360
                 *
ERROR 在行 1:
ORA-02095: 無法修改所指定的初使化參數


SQL> alter system set log_buffer =655360 scope=spfile;

已更改系統.

SQL> alter system set log_buffer =655360 scope=memory;
alter system set log_buffer =655360 scope=memory
                 *
ERROR 在行 1:
ORA-02095: 無法修改所指定的初使化參數


SQL> show parameter log_buff

NAME                                 TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
log_buffer                           integer      524288

SQL> startup force;
已啟動 ORACLE 執行處理.

Total System Global Area 135471092 bytes
Fixed Size                   454644 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 798720 bytes
資料庫已掛載.
資料庫已開啟.
SQL> show parameter log_buffer

NAME                                 TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
log_buffer                           integer      655360

另外一篇英文：
Alter system archive log [start|stop|all|...]

alter system archive log all;
alter system archive log next;
alter system archive log sequence 104;
alter system archive log current;
alter system archive log current noswitch;
The following command can be used to stoparch.
alter system archive log stop
Similarly, arch is started with
alter system archive log start
However, changing the archiver this way doesn't last when the database is restarted. When the database is started, it consultslog_archive_startin the initialization file to determine ifarchis started.
Alter system archive log all

This command is used tomanually archive redo logs.
alter system disconnect session

alter system kill session

alter system kill session 'session-id,session-serial'
This command kills asession. Thesession-idandsession-serialparameters are found in thev$sessionview (columnssidandserial#.
alter system checkpoint

Performs acheckpoint
alter system checkpoint
alter system dump datafile

This command can be used in order todump one ore more blocksof adatafile. The following command dumps blocks 50 through 55 of file 5. Which file 5 is can be found out withv$datafile
alter system dump datafile 5 block min 50 block max 55;
Note:trace filesare only readable by the Oracle account. If you want to change this, set the undocumented initialization parameter_trace_files_publicto true. Doing so will, however, cause abig security risk.
alter system flush buffer_cache

alter system flush buffer_cache;
This command is not available prior to10g. It flushes thebuffer cachein theSGA.
9i had an undocumented command to flush the buffer cache:
alter sessionset events = 'immediate trace name flush_cache';
alter system flush shared_pool

alter system flush shared_pool;
This command flushed theshared pool.
alter system quiesce restricted

alter system suspend|resume

alter system switch logfile

Causes aredo log switch.
alter system switch logfile;
If the database is inarchive log mode, but theARCHprocess hasn't been startedm, the command might hang, because it waits for the archiving of the 'next'online redo log.
alter system register

Forces theregistrationof database information with thelistener.
alter system register
Alter system set timed_statistics

Setting timed_statistics=true might be usefule when usingtk prof.
Alter system set sql_trace

Setting sql_trace=true is a prerequisite when usingtk prof.
Alter system set .... deferred

Alter system can be used to changeinitialization parameterson system level. However, some parameters, when changed with alter system don't affect sessions that are already opened at the time when the statement is executet; it only affects sessions started later. These parameters must be changed withalter system set <initialization parameter> DEFERRED, otherwise aORA-02096: specified initialization parameter is not modifiable with this option error is returned.
These parameters can be identified as they have a DEFERRED in theisses_modifiablecolumn ofv$parameter.
Alter system reset <parameter_name>

Resetsa parameter.
alter system reset some_param scope=both sid='*';
scope

scope=memory

Changes the parameter's value for the running instance only. As soon as the instance is stopped and started, this change will be lost.
scope=spfile

Alters aninitialization parameterin thespfile
scope=both

Alters aninitialization parameterin thespfileas well as in the running instance.