Increase the SHARED POOL SIZE ....
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Increase the SHARED POOL SIZE ....

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Posts
    101
    Hello Friends,

    I want to increasing the shared pool size, when i execute the command i am getting this error:

    My O/S is AIX 4.3
    DB is Oracle9i



    SQL> alter system set shared_pool_size=150m;
    alter system set shared_pool_size=150m
    *
    ERROR at line 1:
    ORA-02097: parameter cannot be modified because specified value is invalid
    ORA-04033: Insufficient memory to grow pool




    Could any one help pls.


    Thanks in advance

    gandhi
    OCP-DBA 8


  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Obviously, you are trying to give a value which would exceed sga_max_size. What is sga_max_size set to? Have a look at that and then set a poper shared_pool_size (< 150M). Or alternatively increase sga_max_size.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what is you sga_max_size?

  4. #4
    Join Date
    Nov 2000
    Posts
    101
    HI,

    My sga_max_size is 30MB.

    I tried to increase the size also. It prompts the same error.

    Could U help me.



    Thanks in Advance

    gandhi
    OCP-DBA 8


  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    How much are those values? Could you please post the init.ora, then I might be able to tell you what's gone wrong.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Dec 2001
    Location
    SAN FRANCISCO, CA
    Posts
    306

    Could u please post the shared pool size parameter of ur database from init.ora file.

    also what is the O/S and what is the memory of the o/s??


  7. #7
    Join Date
    Nov 2000
    Posts
    101
    Hi,

    My O/S is IBM-AIX 4.3.

    DB is Oracle 9.0.1.0.0


    My shared_pool_size=117440512 (from init.ora)

    Could U suggest it to me pls.



    Thanks in Advance,


    gandhi
    OCP8-DBA


  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by gandhi
    Could U suggest it to me pls.
    Yes, sure, but could you post the init.ora file. How much RAM do you have?

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Nov 2000
    Posts
    101
    Hi Julian,

    This is my Init.ora file & my RAM size is 1GB.




    "initORA9011.ora" 28 lines, 1155 characters
    *.aq_tm_processes=0
    *.background_dump_dest='/soft/oracle9/OraHome1/admin/ORA9011/bdump'
    *.compatible='9.0.0'
    *.control_files='/soft/oracle9/OraHome1/oradata/ORA9011/control01.ctl','/s
    oft/oracle9/OraHome1/oradata/ORA9011/control02.ctl','/soft/oracle9/OraHome1/o
    radata/ORA9011/control03.ctl'
    *.core_dump_dest='/soft/oracle9/OraHome1/admin/ORA9011/cdump'
    *.db_block_size=8192
    *.db_cache_size=67108864
    *.db_domain='rs6000'
    *.db_name='ORA9011'
    *.fast_start_mttr_target=300
    *.instance_name='ORA9011'
    #*.java_pool_size='117440512'
    *.job_queue_processes=0
    *.large_pool_size='1048576'
    *.open_cursors=300
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.resource_manager_plan='SYSTEM_PLAN'
    *.shared_pool_size=117440512
    "initORA9011.ora" 28 lines, 1155 characters




    Thanks in Advance,


    gandhi
    OCP8-DBA


  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    SGA_MAX_SIZE
    Parameter type Big integer

    Syntax SGA_MAX_SIZE = integer [K | M | G]

    Default value Initial size of SGA at startup, dependent on the sizes of different pools in the SGA, such as buffer cache, shared pool, large pool, and so on.

    Parameter class Static

    Range of values 0 to operating system-dependent


    you can start adding up your buffer cache, shared pool, large pool, and so on parameters values and set your sga_max_size bigger than that

    Oh btw the DB_CACHE_SIZE is db_block_buffer

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