Re: modifiability of system parameters

If the v$parameter view says TRUE for issess_modifiable, then that parameter can be changed with a simple 'alter session'.

If TRUE for 'issys_modifiable', the command 'alter system' will do the trick.

But any parameter listed as 'DEFERRED' must be modified with the relevant command, plus the word 'deferred' at the end of the command. That makes it true for new sessions only.

Hence alter session set sort_area_size=128000 works a treat. But alter system set sort_area_size=128000 will fail with the same, highly intuitive, error message you got. So the command required is 'alter system set sort_area_size=128000 deferred'. From the report output you included in your original post, it is evident that db_block_checking suffers from the same restrictions.

Regards
HJR "Dino Hsu" <dino1_at_ms1.hinet.net> wrote in message news:br7not0r45qcclmia2miho21o5khpbpgun_at_4ax.com...
> Dear all,
>
> I try to modify a system parameter with 'alter system', but I cannot.
>
> <SQL*plus begin>
>
> SQL> select name, value, isses_modifiable, issys_modifiable,
> ismodified from v$parameter
> 2 where name like '%check%';
>
> NAME VALUE ISSES ISSYS_MOD ISMODIFIED
> ------------------------------ ---------- ----- --------- ----------
> db_block_checksum FALSE FALSE IMMEDIATE FALSE
> log_checkpoint_interval 10000 FALSE IMMEDIATE FALSE
> log_checkpoint_timeout 1800 FALSE IMMEDIATE FALSE
> log_checkpoints_to_alert FALSE FALSE FALSE FALSE
> db_block_checking FALSE TRUE DEFERRED FALSE
>
> SQL> alter session set db_block_checking=false;
>
> Session altered.
>
> SQL> alter system set db_block_checking=true;
> alter system set db_block_checking=true
> *
> ERROR at line 1:
> ORA-02096: specified initialization parameter is not modifiable with
> this option
>
> <SQL*plus end>
>
> <doc begin>
>
> ORA-02096 specified initialization parameter is not modifiable with
> this option
>
> Cause: Though the initialization parameter is modifiable, it cannot be
> modified using the specified command.
>
> Action: Check the Oracle8i Administrator's Guide for information about
> under what scope the parameter may be modified
>
> <doc end>
>
> Anyone knows:
> 1.why can't I modify it with 'alter system'?
> 2.how can we get possible values of system parameters?
>
> Thanks,
> Dino
>
Received on Sun Sep 02 2001 - 00:29:28 CDT