Oracle参数修改小结
Oracle中有些参数是可以在session级别修改,有些则必须在system级别修改,有些参数不需要重启就能马上生效,有些参数必须重启才能生效,那么如何知道这些信息呢?可以从v$parameter视图中得出,重点关注这个视图的以下几列:
ISSES_MODIFIABLE VARCHAR2(5) Indicates whether the parameter can be changed withALTER SESSION(TRUE) or not (FALSE)
ISSYS_MODIFIABLE VARCHAR2(9) Indicates whether the parameter can be changed withALTER SYSTEMand when the change takes effect:
IMMEDIATE- Parameter can be changed withALTER SYSTEMregardless of the type of parameter file used to start the instance. The change takes effect immediately.
DEFERRED- Parameter can be changed withALTER SYSTEMregardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.
FALSE- Parameter cannot be changed withALTER SYSTEMunless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISSES_MODIFIABLE 这一列标志该参数是否可以在session级别被修改;
ISSYS_MODIFIABLE 这一列标志该参数是否可以在system级别被修改,其中有三个值:
IMMEDIATE表示修改完之后立即生效,DEFERRED表示必须得等下个session才能生效,也就是当前session还是不起作用的,FALSE表示实例重启后才能生效。
下面就以4个典型参数做实验:
SQL> select name,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name in ('workarea_size_policy','audit_file_dest','sga_target','sga_max_size');
NAME
------------------------------ ------------------------------ ----------------------------
workarea_size_policy
sga_target
audit_file_dest
sga_max_size
1.workarea_size_policy可以alter session修改
查看原来的配置:
SQL> show parameter workarea_size_policy
NAME
------------------------------------ ----------- -----------------
workarea_size_policy
在session级别修改:
SQL> alter session set workarea_size_policy=MANUAL;
Session altered.
在本session查看,可以发现修改已经生效:
SQL> show parameter workarea_size_policy
NAME
------------------------------------ ----------- -------------------
workarea_size_policy
2. sga_target在用alter system修改后立即生效
查看原来的配置:
SQL> show parameter sga_target
NAME
------------------------------------ ----------- --------------
sga_target
SQL> alter system set sga_target=1400M;
System altered.
用alter system修改后立即生效:
SQL> show parameter sga_target
NAME
------------------------------------ ----------- ------------------
sga_target
3. audit_file_dest在用alter system修改后,知道下个session才生效
查看原来的配置:
SQL> show parameter audit_file_dest
NAME
------------------------------------ ----------- -----------------------
audit_file_dest
注意:后面必须得加关键字deferred,否则会报错。
SQL> alter system set audit_file_dest='H:\INTEL_DB_DUMPS\O02DMS1' deferred;
System altered.
在本session里查询还是原值,没有改变:
SQL> show parameter audit_file
NAME
------------------------------------ ----------- -------------------------
audit_file_dest
重新开个session,在查询发现已经改为新值了:
SQL> show parameter audit_file_dest
NAME
------------------------------------ ----------- -----------------------------
audit_file_dest
4. sga_max_size在用alter system修改后必须重启实例才能生效
查看原来的配置:
SQL> show parameter sga_max_size
NAME
------------------------------------ ----------- -----------------
sga_max_size
注意:后面必须得加scope=spfile,否则会报错。
SQL> alter system set sga_max_size=1400 scope=spfile;
System altered.
如果数据库没重启,无论如何还是原来的配置:
SQL> show parameter sga_max_size
NAME
------------------------------------ ----------- ------------
sga_max_size
重启数据库:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1468006400 bytes
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
Database opened.
再重新查询,就可以看到用的是新值了:
SQL> show parameter sga_max_size
NAME
------------------------------------ ----------- -------------
sga_max_size