Ora-02095
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Ora-02095

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    Ora-02095

    I am trying to modify some memory parameters.
    My total SGA is 400M which is equal to the sga_max_size.
    I would like to add 200M to the db_cache_size.
    I tried setting SGA_MAX_SIZE=600M with 'alter system set sga_max_size=600M' and I am getting the ora-02095. I am not using an spfile.
    Metalink does not have enough info on this problem.
    Please help. Thanks.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you cant change it on the fly - you need to update your init.ora of not using a spfile and restart the database

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    Ok. Yes I realized that SGA_MAX_SIZE cannot be modified dynamically.
    However, I have db_cache_size of 132M. I tried to increase it to 250M.
    'Alter system set db_cache_size=250M' and I get a ORA-02097 error.
    Is this because I have no spfile ? Thanks.

  4. #4
    Join Date
    Dec 2007
    Posts
    55
    If you are making use of pfile then shutdown your db make the changes in your init.ora(pfile) and startup with pfile,you will see teh changes or else create spfile from your pfile,startup with spfile then cahnge parameters with scope=spfile as sga_max_size is not a dynamic parameter you need to bounce db so that the changes are reflected in your next session.

  5. #5
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    Ok. Does this mean that the only way I can modify dynamic parameters is when a spfile is present ? and if no spfile is present then I need to change the pfile and restart ?

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    they arent dynamic parameters

  7. #7
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218
    db_cache_size is a dynamic parameter ? I should be able to modify this one ?

  8. #8
    Join Date
    Oct 2006
    Posts
    175
    what is your shared_pool_size? Check if adding new db_cache_size exceeds total SGA? Since you haven't posted all error message, I am just pointing at a possibility. And you said, you don't have a spfile, then why don't you open your InitSID.ora and specify the value there. You can't run alter system.. to modify system parameters if you don't have a spfile.
    Thanks,
    gtcol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width