Thursday, January 14, 2021

ORA-04031: unable to allocate nn bytes of shared memory

You will receive ORA-04031 error when allocated memory is small too small and does not have enough system global area (SGA).

Recently I got below error...

ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer") < ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS_INTERNAL"
< ORA-06512: at line 1
< ORA-04031: unable to allocate 63176 bytes of shared memory ("shared pool","DBMS_STATS_INTERNAL","PLMCD^60dfd26c","BAMIMA: Bam Buffer")

Please find the cause and action

ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the DBMS_SHARED_POOL package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE. If the error is issued from an Oracle Streams or XStream process, increase the initialization parameter STREAMS_POOL_SIZE or increase the capture or apply parameter MAX_SGA_SIZE.


I will explain some of the possible root causes and how to fix it.

1. Insufficient RAM – This is most common issue when the database usage increased over the period. Increase the RAM and also adjust related SGA_MAX_TARGET or MEMORY_MAX_SIZE in case of AMM.

2. Shared pool fragmentation – This can be addressed by increasing the shared_pool_size in spfile or init.ora parameter. If you cannot bounce the database for changes to effect then I flush shared pool using “alter system flush shared pool;” command for temporary fix

3. Pinned packages – You will see this error if you have pinned lots of packages with dbms_shared_pool.keep and DB does not have enough resources

If your application is not using bind variables then it will cause SGA to become fragmented. In this case, if you turn on cursor_sharing it will help. Also make sure you use bind varibales. 


Also, run below queries to get SGA/PGA memory advice.
select * from v$sga_target_advice; select * from v$pga_target_advice;

Refer:
MOSC notes 146599.1 and 396940.1 for more details for resolving the ORA-04031 error:

Regards,
http://oracleracexpert.com

30 comments:

  1. Agra Call Girl and Female Model Escort Services in Agra for your spicy erotic desires. Agra Escorts is the best Escorts Girls service in Agra free and paid sex incall and outcall independent agency. If you are interested please contact me for more information visit our website.

    ReplyDelete
  2. Wow that is really a great post (review) to share. I really do like your review post.
    LEARN FROM SCRATCH ONLINE BEGINNER MUSIC LESSON Online Music Lessons

    ReplyDelete
  3. Most popular Independent Escorts Service in Mahipalpur, We are providing best Mahipalpur Escorts, Call Girls, Female Independent Escorts low and high price available like a sexy, hot, beautiful, charm, etc. If you are interested please call me and for more information visit our website.
    Escorts In Mahipalpur

    ReplyDelete
  4. 3D Rendering Services | Quickgunz |3D Rendering company
    Quickgunz Pvt Ltd are engaged in offering our clients 3D Rendering Services.Our 3d rendering company provide architecture, simulators, video games, films and television visual effects and design visualisation hire us for pocket friendly prices
    3D Rendering Company
    3D Rendering services

    ReplyDelete
  5. Packers and Movers Bangalore - Reliable and Verified Household Shifting Service Providers Give Reasonable ###Packers and Movers Charges. Cheap and Best Office Relocation Compare Quotation for Assurance for Local and Domestic House Shifting and Get estimates today to save upto 20%, ***Read Customer Reviews - @ Packers And Movers Bangalore

    ReplyDelete
  6. Nisha Arora Delhi escort service is always top class and highly professional. Delhi is one of the most visiting areas a place of full enjoyment for youth. Many people come to this place for enjoyment and weekend celebration with friends and many of them looking for escort service to refresh their mind and fulfill their physical need.
    Escorts in Delhi
    Call girls in Delhi
    Delhi escort
    Delhi Escorts Service

    ReplyDelete
  7. Hello I am Mansi Working with Manali escorts service. Tremendous quantities of free ladies are working in the escorts administrations in Manali

    for the entire day and night. On the off chance that you are feeling so alone or get disappointed from the furious life plans, at that point

    nothing is in a way that is better than having a friend who can give you a badge of affection and bliss.She is a free Escorts model will give you

    more joy and Full satisfaction.

    https://www.mansiescorts.com



    manali Call Girls
    manali escorts

    amritsar Call Girls
    amritsar escorts

    ambala Call Girls
    ambala escorts

    agra Call Girls
    agra escorts

    ahmedabad Call Girls
    ahmedabad escorts

    almora Call Girls
    almora escorts

    baddi Call Girls
    baddi escorts

    bharuch Call Girls
    bharuch escorts

    Chamba Call Girls
    Chamba escorts
    call girl in Chamba

    Chitkul Call Girls
    Chitkul escorts
    call girl in Chitkul

    ReplyDelete
  8. Our Ambala Escort Girls is fully matured to bring immense joy to your life and Get fully satisfied with Our Hot Girls in a luxury hotel -

    Ambala Call Girls | Escorts in Ambala | Ambala Escorts

    ReplyDelete
  9. Call girls in Dwarka available 24*7 at your service, you just need to pick your phone and call or What Sapp to Dwarka escort service to book these hot call girls Dwarka. They all are very beautiful and well maintained figure. Just call best Dwarka escorts and get those in arms full night.
    Delhi call girls
    Chandigarh escorts
    Dera bassi escorts
    Kalka escorts service
    Baddi escort service

    ReplyDelete
  10. Date the prettiest Call girls in Karol Bagh and get blown with 100% satisfaction and
    grace. Our girls are completely trained and equipped to entertain you in the best way. Call the karol Bagh escort service today!
    Karol Bagh Escorts
    Geeta Colony escort service
    call girls pinjore
    call girls Jahangirpuri
    call girls shastri nagar

    ReplyDelete
  11. jack is from seattle where he works as an health specialist at Arrowmeds. He has 6 years of experience with ED with a focus on ED Solveing. In his spare time, jack enjoys Help of my patients in my hospital.
    Fildena 100
    || Cenforce 100 ||Vidalista 20

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Cenforce 200mg and Cenforce 150​​ are for the treatment of erectile dysfunction in men. Some type of sexual incitement is required for an erection to happen with this medication. ​If anyone is facing these issues then must visit our website and enjoy your life.​​

      Alternative tablets Tadacip 20, Caverta 100, Vidalista 60, and Vilitra should be taken by men with erectile dysfunction in order to improve their sex life. Medic Scales your trusted online Pharmacy specialists in Erectile Dysfunction issues. so we have all types of ED pills in our Store. Buy Online now. Tadaflo 20mg

      Delete
  12. jack fron has worked in the health industry for 6 years, gaining experience in health specialist. As a seasoned Doctor, he is passionate about ED Expert. In addition to health, he is also involved
    cenforce pill ||cenforce 25mg ||cenforce 50mg

    ReplyDelete
  13. Telephone Number. The First Time You Open A Browser, Then Open The Official Delta Airlines Website. After That, Click The ” Contact Us” Button. Choose The Phone Phone Number And Then Follow The Instructions. The Window With The Hint Will Be Displayed With The Telephone Phone Number Is Shown. All You Have Be Doing Is To Dial The Hotline Number.

    Delta Airlines Phone Number +1(855)645-2011
    Delta Airlines Reservations And Phone Number. Delta Airlines Phone Number To Instantly Resolve A Variety Of Issues Related To Flight. Delta Airlines Is Of The Most Famous And Most Significant Delta State-Primarily Completely Airways That Was Established The Airline In Chicago, Illinois.

    Delta Airlines Customer Service Phone Number +1(855)645-2011
    To Make Your Reservations Or Provide Your Payment Information Via Phone To Complete The Reservation Or Provide Your Payment Information Over The Phone, Call Delta Reservations In The U.S. And Canada By Dialing +1(855)645-2011 Delta Or You Can Contact The Local Delta Reservations Office. If You Wish To Have The Delta Representative Contact You, Click The Link Below (Available Only In Only The U.S. And Canada Just).


    Delta Reservations In The U.S.

    Delta airlines +1(855)645-2011 phone number
    Delta airlines +1(855)645-2011 customer service phone number
    Delta Airlines +1(855)645-2011 toll-free number
    Delta Airlines +1(855)645-2011, official website
    Delta airlines +1(855)645-2011 manage booking
    Delta Airlines +1(855)645-2011 make my reservations for flights

    ReplyDelete
  14. It is time for the web to be worthwhile for your business, at our net improvement employer it is our predominant goal to assist your commercial enterprise develop via the web and make the most of your investment. We create extraordinary web site layout for small groups that power today’s customers. In order to create treasured experience, we improve sturdy connection with our clients, at each contact point. Our passionate net designers & builders work challenging to beautify purchaser experience, generate excessive pleasant leads, develop your on line presence and unravel your enterprise degree challenges via innovative, innovative and Low value net designs.

    At Digitaleralab, our Affordable web designing Services in USA net format offerings will make certain your enterprise is extensively diagnosed and seize extra audiences with a stellar and expert web site design. We are well-known as the net layout business enterprise that ensures your small enterprise web diagram is fairly created. Creative small enterprise web site design, created through the main net plan company, boasts different programs and truthful pricing to create company HTML, CSS, Java, JavaScript, Python, WordPress, Magento, etc. options that excels, request number +91 8860149017.


    Our net format organisation boasts a crew of professional net designers and carrier carriers who will go to all lengths in making sure that your layout is smooth and up-to date.


    TIMELY SERVICES

    At our web sketch agency, low cost net format offerings in a time-efficient manner lie at the core of our service. We supply the favored effects proper on time.

    TOP-NOTCH INDUSTRY KNOWLEDGE

    Every small enterprise web site design, created via our plan firm, is created utilized top-notch enterprise information and knowledge of our committed designers.

    In this on line world, an appealing low fee website graph helps you to comprise science in a way that your commercial enterprise excels. Ever-informed clients of these days constantly appear for a small commercial enterprise web site layout with and an intuitive consumer experience. Creating a Affordable web designing Services in USA for your commercial enterprise has come way in advance than simply augmenting an ecommerce business. A expert web site design, created by means of our skilled designers, possesses the electricity to seriously change your business. Ever seeing that web has advanced, masses of diagram companies are coming up with top-notch, mobile-friendly and dynamically displayed and beautiful websites to interact customers. As USA’s affordable and cheap net graph company, our net designers supply net plan offerings to sketch a lovely web site that will increase conversions and consumer engagement. Expert marketing consultant presents small commercial enterprise web site layout on line carrier that are search engine optimisation optimize graph to depart anybody awestruck. At digitaleralab, all our net designers use their innovative instincts in writing high-quality coding to create a web site that stands at par with the first-rate of code and follows an interactive design. We, USA’s #1 HTML, CSS, Java, JavaScript, Python, WordPress, Magento, etc. small commercial enterprise web site sketch provider, observe the pleasant sketch manner to raise out web improvement that possesses the exceptional homepage, web page layout, colors, UI/UX graph that is Google-friendly.

    ReplyDelete
  15. Are you experiencing Erectile dysfunction and are looking for a secure and effective treatment? Vidalista 20mg is an option to think about. Vidalista has been identified as an ineffective medication that contains Tadalafil which is the active ingredient in the most often prescribed ED drugs of the present.
    vidalista 80
    Vidalista 40

    ReplyDelete
  16. How To Contact Bitcoin ATM Customer Service Number?

    Bitcoins are the cryptocurrency without a bank system is required or even the assistance from the authorities. Bitcoin is a applications that are it is easy to access. It is essential to be sure that you are investing in Bitcoin ATM Customer Service Number. It's one of the best opportunities for business growth. A lot of people invest in bitcoin markets to improve their financial position. Bitcoins are the most effective option to improve your financial standing with just a only a few investments.

    Bitcoin ATM Support Phone Number

    Bitcoin ATM Customer Service Number

    ReplyDelete
  17. How To Contact Bitcoin ATM Customer Service Number?

    The Digital World Has Transformed a lot, but it is still exposed to certain risks and technical problems. This is also true for the Bitcoin Wallet. Therefore, whenever you confront issues when using a bitcoin wallet, call the Bitcoin Support Phone Number without hesitation. The expert will guide you to resolve the issues in a short time and let you get access back to the wallet.

    Bitcoin ATM Customer Service Phone Number

    Customer Service For BTC ATM

    Ethereum Customer Service Number

    Bitcoin Support Phone Number

    Bitcoin ATM Customer Service Phone Number

    Bitcoin Customer Support Number

    ReplyDelete
  18. How To Use Gemini Exchange?

    How To Use Gemini Exchange?People looking for the best Cryptocurrency Exchange. are asking How To Use Gemini Exchange. The main reason is that it is now performing as the leading platform among every individual, but few people new to it are not familiar with using it.

    ReplyDelete
  19. If you cannot access the Coinbase account and make a transfer, then you can immediately contact Coinbase Customer Service. These are a suitable option for availing the experts for resolving any issues in your Coinbase account.

    ReplyDelete
  20. IT Recruitment Agency India
    Why our IT Recruitment Consultants in India is famend due to the fact of our quick, respectable and correct delivery. Our retention of the proper govt intelligence is absolutely out of the world, thus, without problems unfold happiness and joy. Our well-trained recruitment consultants are from IT backgrounds and they try to exceed the clients’ expectations through terrific selections. Candidates then for my part name by means of these IT recruitment experts and they make positive to go with the private interviews, written tests, psychological tests, reference check, scientific assessments and others to supply the first-rate candidates to the companies.

    There are a range of IT sectors we cowl to recruit proper guys on the proper posts, like-

    Application development area of interest is some thing very stressful and most of the software program organizations are searching for the proper candidates. Using our IT staffing services, any small, medium and massive software program agencies can count on to get the nice developer to work on any type of projects. Our IT professionals very properly be aware of about A-Z attributes, which are vital to be aware of via all the candidates, like- analysis, design, implementation, checking out and integration of the systems. Not solely this, our IT recruitment advisor makes certain to discover these candidates who can fortunately work in any cooperative improvement project, except any hassle. Our professionals are ideal in making up a world community to supply the abilities and brain you are searching for.

    With a extensive vary of journey in IT sector, we can create options for assembly the most skilled Website Developer. Our IT recruitment organization combines deep science know-how and software administration abilities to assist all the candidates to make them apprehend with the commercial enterprise ethics. Moreover, our crew works in optimizing the full viable of folks via motivating them in the boosting their profession aspirations. This way, our IT recruitment business enterprise India is in a position to create the first-class IT Recruitment Consultants in India for your IT association to improve your commercial enterprise and take it to the subsequent level.

    Business Intelligence & Data Warehousing is once more very a great deal in demand and we have the satisfactory candidates who can effortlessly work for your organisation in the very identical post. In this area of interest a set of brilliant strategies, applications, statistics and techniques normally use so that the series and dissemination of enterprise records work can be done. Data is very a good deal vital in any type of business, however, we bought the exceptional human beings at work who will clearly assist in jogging brilliant commercial enterprise operations so easily. For IT candidates, we have limitless possibilities for them to obtain boom and success as nicely as for the IT firms.

    Being an IT manpower consultancy, we have some thing for the Project Managers. Yes, we are in many instances in conversation with so famend IT corporations which continually seem for the great applicants. We display screen the pleasant out of many candidates who received brilliant ability set, including- software of knowledge, trip in coping with incredible equipment and methods and others to meet the challenge requirements. Aside this, we additionally recruit candidates for alternate management, ERP and CRM, testing, architecture, and a number others.

    ReplyDelete