Oracle中有些参数是可以在session级别修改,有些则必须在system级别修改,有些参数不需要重启就能马上生效,有些参数必须重启才能生效,那么如何知道这些信息呢?可以从v$parameter视图中得出,重点关注这个视图的以下两列:
ISSES_MODIFIABLE VARCHAR2(5) Indicates whether the parameter
can be changed with ALTER SESSION(TRUE) or not (FALSE)
ISSYS_MODIFIABLE VARCHAR2(9) Indicates whether the parameter
can be changed with ALTER SYSTEM and when the change takes effect:
IMMEDIATE- Parameter can be changed with ALTER SYSTEM regardless of
the type of parameter file used to start the instance. The change
takes effect immediately.
DEFERRED - Parameter can be changed with ALTER SYSTEM regardless of
the type of parameter file used to start the instance. The change
takes effect in subsequent sessions.
FALSE- Parameter can not be changed with ALTER SYSTEM unless 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 ISSES_MODIFIABLE ISSYS_MODIFIABLE
------------------------ -------------------- ------------------------
workarea_size_policy TRUE IMMEDIATE
sga_target FALSE IMMEDIATE
audit_file_dest FALSE DEFERRED
sga_max_size FALSE FALSE
--1.workarea_size_policy可以alter session修改
--查看原来的配置:
SQL> show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- -----------------
workarea_size_policy string AUTO
--在session级别修改:
SQL> alter session set workarea_size_policy=MANUAL;
Session altered.
--在本session查看,可以发现修改已经生效:
SQL> show parameter workarea_size_policy
NAME TYPE VALUE
------------------------------------ ----------- -------------------
workarea_size_policy string MANUAL
--2. sga_target在用alter system修改后立即生效
--查看原来的配置:
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- --------------
sga_target big integer 1504M
SQL> alter system set sga_target=1400M;
System altered.
--用alter system修改后立即生效:
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------
sga_target big integer 1400M
--3. audit_file_dest在用alter system修改后,知道下个session才生效
--查看原来的配置:
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- -----------------------
audit_file_dest string H:\INTEL_DB_DUMPS
--注意:后面必须得加关键字deferred,否则会报错。
SQL> alter system set audit_file_dest='H:\INTEL_DB_DUMPS\O02DMS1' deferred;
System altered.
--在本session里查询还是原值,没有改变:
SQL> show parameter audit_file
NAME TYPE VALUE
------------------------------------ ----------- -------------------------
audit_file_dest string H:\INTEL_DB_DUMPS
--重新开个session,在查询发现已经改为新值了:
SQL> show parameter audit_file_dest
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
audit_file_dest string H:\INTEL_DB_DUMPS\O02DMS1
--4. sga_max_size在用alter system修改后必须重启实例才能生效
--查看原来的配置:
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -----------------
sga_max_size big integer 1504M
--注意:后面必须得加scope=spfile,否则会报错。
SQL> alter system set sga_max_size=1400 scope=spfile;
System altered.
--如果数据库没重启,无论如何还是原来的配置:
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- ------------
sga_max_size big integer 1504M
--重启数据库:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1468006400 bytes
Fixed Size 1303076 bytes
Variable Size 612371932 bytes
Database Buffers 847249408 bytes
Redo Buffers 7081984 bytes
Database mounted.
Database opened.
--再重新查询,就可以看到用的是新值了:
SQL> show parameter sga_max_size
NAME TYPE VALUE
------------------------------------ ----------- -------------
sga_max_size big integer 1400M