Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2003
    Posts
    21

    Red face Unanswered: Oracle Shared Memory Error while connecting to database ORA-04031

    Hello,

    I have been getting this problem while connecting to ORacle database.

    I try to connect to oracle as follows:

    sqlplus /nolog

    connect / as sysdba

    When I type the above command, I get the following error messages:

    ERROR:
    ORA-00604: error occurred at recursive SQL level 2
    ORA-04031: unable to allocate 4200 bytes of shared memory ("shared
    pool","TRIGGER$","sga heap","state objects")


    when I do ps -aux, I see around 30 processes running in the background as follows:

    oracle 27944 0.0 0.5 100628 21864 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27949 0.0 0.5 100604 21732 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27954 0.0 0.5 100648 23412 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27959 0.0 0.4 100596 18328 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27961 0.0 0.6 100808 24684 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27963 0.0 0.5 100616 20460 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27965 0.0 0.5 100652 20700 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27970 0.0 0.5 100604 23164 ? S Apr29 0:01 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27972 0.0 0.6 100632 23684 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 27984 0.0 0.5 100756 21488 ? S Apr29 0:00 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))
    oracle 28112 0.0 0.3 100416 15352 ? S Apr29 0:37 oracledb (DESCRIPTION=(LOCAL=no)(ADDRESS=(PROTOCOL=BEQ)))

    what these processes are all about ?


    What could be the possible solution to this problem ?


    Thanks,

    - Bikram.
    Last edited by bikramjeet77; 07-16-03 at 14:22.

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Connect as SYS

    Hi
    I wanted to compare the levels of access in Oracle with that in Linux.

    I see people asking to connect to Oracle as SYS, SYSDBA.
    What does that mean, and what extra access features does each priviledge come with.
    Is there any documentation too, on this particular feature.
    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You sga has become too fragmented. Oracle is trying to load something
    into the sga, and the free space available is so fragmented that there
    is not enough contiguous free space to hold the piece trying to be loaded.

    - You need to restart the database to clear up the problem...
    - You should possible increase the size of the SGA to something more
    reasonable.
    - You should look at objects that are getting flushed out of the SGA and
    consider pinning them into shared memory ...

    Ex: Script to look at objects:

    To determine what large PL/SQL objects are currently loaded in the shared pool
    and are not marked 'kept' and therefore may cause a problem, execute the following:
    select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    from v$db_object_cache
    where sharable_mem > 100
    and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    or type = 'PROCEDURE')
    and kept = 'NO'
    order by owner,loads desc;


    --- Objects that have been reloaded ---

    select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    from v$db_object_cache
    where loads > 1
    and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    or type = 'PROCEDURE')
    and kept = 'NO'
    order by owner,loads desc;


    You can then pick the objects that have the largest requirements and
    pin them into SGA with the following:

    DBMS_SHARED_POOL.KEEP(v_pin_object)

    I actually have a database startup trigger that pins my objects...

    Hope this helps
    Gregg

  4. #4
    Join Date
    Jul 2003
    Posts
    21

    Oracle Shared Memory Error ORA-04031

    Hi Gregg,

    Thanks a lot for your help.

    But I have one more question.

    Since I have not been able to connect to the database even as SYSDBA,
    I cannot really issue a shutdown command.

    Whenever I try to connect to database using the following command,

    connect / as sysdba

    it gives me the ORA-04031 error.


    Other than restarting the database server, is there any other way that I can shutdown and start the database ??

    Thanks,

    - Bikram.



    Originally posted by gbrabham
    You sga has become too fragmented. Oracle is trying to load something
    into the sga, and the free space available is so fragmented that there
    is not enough contiguous free space to hold the piece trying to be loaded.

    - You need to restart the database to clear up the problem...
    - You should possible increase the size of the SGA to something more
    reasonable.
    - You should look at objects that are getting flushed out of the SGA and
    consider pinning them into shared memory ...

    Ex: Script to look at objects:

    To determine what large PL/SQL objects are currently loaded in the shared pool
    and are not marked 'kept' and therefore may cause a problem, execute the following:
    select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    from v$db_object_cache
    where sharable_mem > 100
    and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    or type = 'PROCEDURE')
    and kept = 'NO'
    order by owner,loads desc;


    --- Objects that have been reloaded ---

    select substr(owner,1,10),substr(name,1,32), sharable_mem,loads,executions
    from v$db_object_cache
    where loads > 1
    and (type = 'PACKAGE' or type = 'PACKAGE BODY' or type = 'FUNCTION'
    or type = 'PROCEDURE')
    and kept = 'NO'
    order by owner,loads desc;


    You can then pick the objects that have the largest requirements and
    pin them into SGA with the following:

    DBMS_SHARED_POOL.KEEP(v_pin_object)

    I actually have a database startup trigger that pins my objects...

    Hope this helps
    Gregg

  5. #5
    Join Date
    Apr 2001
    Location
    Netherlands
    Posts
    191
    the dirty way.. kill a process (like the pmon process) and wait a minute...

    beware .. this could be risky (might require recovering afterwards...)
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can try to

    alter system flush shared_pool;

    but I don't think that will help you out.... You will have to find
    someone that can connect as internal and shutdown the database
    to free the SGA

    Gregg

  7. #7
    Join Date
    Jul 2003
    Posts
    21
    Gregg,

    What happens if I try to use ipcrm to free the shared memory ??

    ipcrm shmid

    Would that do good ?

    Because I still wont be able to issue the command,

    alter system flush shared_pool;

    since I cannot get connected to the database.

    and ya the server that I had been working on while testing is the secondary backup server.

    Thanks,

    - Bikram.



    Originally posted by gbrabham
    You can try to

    alter system flush shared_pool;

    but I don't think that will help you out.... You will have to find
    someone that can connect as internal and shutdown the database
    to free the SGA

    Gregg

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I'm not sure if that would work or not ...

    You should be able to login thru:

    svrmgrl .... connect internal/xxxx@srvcname
    shutdown immediate

    Gregg

  9. #9
    Join Date
    Nov 2003
    Posts
    5

    alter system flush shared_pool

    Can someone explain what "alter system flush shared_pool" do to the running query, active execution plans, etc.?

  10. #10
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    Try physically granting unlimited quota on SYSTEM to both SYS and SYSTEM. There is in fact initial limitations with the System tablespace. With it being recursive sql I doubt very much if this is a problem with the user that is actually executing the statement.
    SATHISH .

  11. #11
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Originally posted by satish_ct
    Hi,

    Try physically granting unlimited quota on SYSTEM to both SYS and SYSTEM. There is in fact initial limitations with the System tablespace. With it being recursive sql I doubt very much if this is a problem with the user that is actually executing the statement.
    THE CURRENTLY RUNNING QUERIES ARE UNAFFECTED.

  12. #12
    Join Date
    Nov 2003
    Posts
    5
    Do "alter system flush shared_pool" free up contiguous memory?

  13. #13
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    Hi
    It clears the cached Data Dictionary,
    Shared SQL and PL/SQL areas for SQL statements,
    stored procedures, function, packages, and triggers

    regards
    Shelva

Posting Permissions

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