Re: SGA_TARGET

rubanp_at_gmail.com wrote:
> I have the following error when i try to increase my sga_target in 10g.
>
> SQL> show parameter sga
>
> NAME TYPE VALUE
> ------------------------------------ -----------
> ------------------------------
> lock_sga boolean FALSE
> pre_page_sga boolean FALSE
> sga_max_size big integer 160M
> sga_target big integer 160M
>
> SQL> show parameter timed_statis
>
> NAME TYPE VALUE
> ------------------------------------ ----------- --------------
> timed_statistics boolean TRUE
> SQL>
>
> SQL> alter system set sga_target=170M;
> alter system set sga_target=170M
> *
> ERROR at line 1:
> ORA-02097: parameter cannot be modified because specified value is
> invalid
> ORA-00823: Specified value of sga_target greater than sga_max_size
>
>
> SQL> alter system set sga_target=180M;
> alter system set sga_target=180M
> *
> ERROR at line 1:
> ORA-02097: parameter cannot be modified because specified value is
> invalid
> ORA-00823: Specified value of sga_target greater than sga_max_size
>
>
> Regards
> Ruban.P
> India

And which part of this error message: "ORA-00823: Specified value of sga_target greater than sga_max_size" - you don't understand? You have sga_max_size=sga_target=160m (which most probably means that you didn't set it explicitly and Oracle set it to default value,) so you can't alter sga_target to a larger value dynamically. You need to use SCOPE=SPFILE clause of ALTER SYSTEM and restart the instance for new sga_target setting to take effect.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Mon Sep 25 2006 - 02:09:07 CDT