ORA-04030 while running jobs through dba_jobs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: ORA-04030 while running jobs through dba_jobs

  1. #1
    Join Date
    Nov 2001
    Posts
    42

    Smile ORA-04030 while running jobs through dba_jobs

    Hello All,

    I am getting ORA-04030 errors daily with jobs that are scheduled to run through dba_jobs using dbms_sql. The same jobs complete without errors when they are run manually.

    I have tried to work with Oracle but I have not got any success , even I tried lots of things ……(using Oracle notes and my own experience) but without success, I hope you guys can help me to resolve this error….

    Here are the details of my server .

    OS AIX5L Based Systems (64-bit / Memory 16GB

    Oracle - Oracle Server - Enterprise Edition 10.1.0.5

    Oracle user has unlimited ulimit

    /optware/oracle$ ulimit -Ha
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) unlimited
    stack(kbytes) 4194304
    memory(kbytes) unlimited
    coredump(blocks) unlimited
    nofiles(descriptors) unlimited

    Some of the important parameters from init.ora

    resource_limit = TRUE
    sga_max_size = 4194304000
    __shared_pool_size = 1895825408
    shared_pool_size = 1895825408
    __large_pool_size = 318767104
    large_pool_size = 318767104
    __java_pool_size = 419430400
    java_pool_size = 419430400
    shared_pool_reserved_size= 50677721
    sga_target = 4194304000
    db_block_size = 8192
    __db_cache_size = 1543503872
    db_cache_size = 838860800
    compatible = 10.1.0.4.0
    job_queue_processes = 25
    cursor_sharing = SIMILAR
    parallel_min_servers = 0
    parallel_max_servers = 5
    hash_area_size = 1310720
    sort_area_retained_size = 0
    open_cursors = 500
    pga_aggregate_target = 3145728000

    Please let me know if you need any more details.

    Santo

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    1- You probably already did it but just to be sure, don't forget to check "Note 233869.1 Diagnosing and Resolving ORA-4030 errors".

    2- Be sure you set ALL unlimited values for both -ha and -sa.

    3- If your offending code relies in bulk collect, try the code without it. In a 10g environment -even after working a TAR for two month- our only practical solution was to get rid of bulk collect to avoid the error.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2001
    Posts
    42
    Quote Originally Posted by PAVB
    1- You probably already did it but just to be sure, don't forget to check "Note 233869.1 Diagnosing and Resolving ORA-4030 errors".

    2- Be sure you set ALL unlimited values for both -ha and -sa.

    3- If your offending code relies in bulk collect, try the code without it. In a 10g environment -even after working a TAR for two month- our only practical solution was to get rid of bulk collect to avoid the error.

    PAVB

    Thanks for your prompt reply.


    1- Yes I went through 233869.1 Note id, Made some changes also but no success, “There can be issues with other OS parameter settings such as maxuproc” , I am not sure how oracle link 4030 with Maxuproc process, Oracle not able to justified this….anyway my current setting for this parameter is 2048

    2- Hard ulimit and soft ulimit shows different values but our SA say that this is maximum and we cant go beyond this, Everything is set unlimited? Any suggestion on this from your side?

    abc:/optware/oracle$ ulimit -Ha
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) unlimited
    stack(kbytes) 4194304
    memory(kbytes) unlimited
    coredump(blocks) unlimited
    nofiles(descriptors) unlimited

    abc:/optware/oracle$ ulimit -sa
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) 16777216
    stack(kbytes) 4194304
    memory(kbytes) 16777216
    coredump(blocks) 2097151
    nofiles(descriptors) unlimited


    3) I had open STR number of times, every time I got different answer....but finally I got only one answer.....apply latest patches....I did that also.......but still problem...is same....

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    1- In our case we set all hard and soft values to unlimited, which solved the issue on AIX/9i... it didn't on Linux/10g

    2- To work around the issue on Linux/10g we made changes to the application getting rid of bulk collect operations.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    what is the value of WORKAREA_SIZE_POLICY?..

    also PS the min values assigned sounds too high and you also have a sga_max/sga_target specified.. so memory left for PGA is practically too low though you have set it to ~3G.

    Suggestion: You any way have turned automatic sizing of different pools by setting SGA_TARGET.. i suggest you lower the values for Shared_Pool to about 400M, db_cache_size to 400M, Large_Pool to about 200M..


    Rgds
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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