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

ORA-39095: Dump file space has been exhausted: Unable to allocate 8192 bytes

Uses can receive below error during the export in case if you did not provide enough file names

$expdp xxx/xxxx directory=EXP_DIR DUMPFILE=exp_user.dmp LOGFILE=exp_user.log SCHEMAS=EMPUSER FILESIZE=20m
…..

ORA-39095: dump file space has been exhausted: unable to allocate 4096 bytes
Job "EMPUSER"."sys_export_table_01" stopped due to fatal error at 10:05:03

You will also receive this error if you specify one dump file or a number less than parallelism value. The slave processes locking the file does not release the lock even after finishing as it wait other processes to write to the file.

$expdp xxx/xxxx directory= EXP_DIR DUMPFILE=exp_user.dmp LOGFILE=exp_user.log SCHEMAS=EMPUSER PARALLEL=5

ORA-39095: dump file space has been exhausted: unable to allocate 8192 bytes

DONOT use PARALLEL clause to avoid this error.

In case if you have space issue on file system, then you can generate multiple dumps on multiple file systems or disks.


Thanks,
http://oracleracexpert.com
Oracle ACE

Oracle 19c Security New Features

This webinar is intended for database administrators (DBAs), security admins, developers, and others tasked with performing operations securely and efficiently.

Join the Webinar to learn New Security Features in Oracle 19c

Date and time: Dec 18th 2020 7:00am-8:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com

DML operations on Active Data Guard standby in Oracle 19c

The DML operations is an Active Data Guard only feature enables DML operations on the standby database to be redirected to the primary to accommodate infrequent writes by reporting applications running against Active Data Guard standby database.

This DML feature introduced in Oracle 19c, this includes DML statements that are part of PL/SQL blocks.

The DML on the standby database will have below steps

• The user executes DML against open standby database.
• DML will be automatically redirects to the primary database.
• The DML will be applied on the primary Datable
• The redo info related change is streamed back to the standby database.
• The application change based redo info completes the DML and data is available for the client.

There are two methods to configure DML Redirection with the help of ADG_REDIRECT_DML parameter.

1. The DML Redirect can be configured at system level, this will apply to all sessions connection to the standby database

Run below command for SYSTEM LEVEL DML Redirection
SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=TRUE SCOPE=BOTH;

2. You can overwrite the system level parameter with alert session command to enable the DML redirect for the current session only.

Run below command to enable SESSION LEVEL DML Redirection 

SQL>ALTER SESSION ENABLE ADG_REDIRECT_DML;

Thanks & Regards

http://oracleracexpert.com

 

Oracle RAC and Grid new features in 19c

Oracle Database 19c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 19c

Join the Webinar to learn New Features in Oracle 19c RAC and Grid

Date and time: Nov 25th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)


To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com. Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks & Regards
http://www.oracleracexpert.com