ORA-04031

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select increment$,minvalue,m...","sga heap(3,0)","kglsim heap")

Solution:-

# ps -ef|grep oracle

find the smon and kill the pid for it.

# Kill -9 

SQL> startup mount

ORACLE instance started.

Total System Global Area 4831838208 bytes Fixed Size 2027320 bytes Variable Size 4764729544 bytes Database Buffers 50331648 bytes Redo Buffers 14749696 bytes Database mounted. SQL>

SQL> alter system set shared_pool_size=100M scope=spfile;

System altered.

SQL> shutdown immediate

ORA-01109: database not open

Database dismounted. ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 4831838208 bytes Fixed Size 2027320 bytes Variable Size 4764729544 bytes Database Buffers 50331648 bytes Redo Buffers 14749696 bytes Database mounted. Database opened.

SQL> create pfile from spfile;

File created.

OR

Oracle 11G

For implementation in Oracle 11g startup database in mount mode:

$ export ORACLE_SID=[instance]
$ sqlplus sys as sysdba

SQL> startup mount
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.

SQL> alter system set memory_max_target=2000m scope=spfile;
System altered.

SQL> alter database open;
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened. 

SQL> alter system set memory_target=1600m;
System altered.

Oracle 10G

 For implementation in Oracle 10g startup database in mount mode:

$ export ORACLE_SID=[instance]

$ sqlplus sys as sysdba

SQL> startup mount
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.

SQL> alter system set sga_max_size=2000M scope=spfile;
System altered.

SQL> alter database open;
ORACLE instance started.
Total System Global Area 1000189952 bytes
Fixed Size                  1337492 bytes
Variable Size             708839276 bytes
Database Buffers          285212672 bytes
Redo Buffers                4800512 bytes
Database mounted.
Database opened. 

SQL> alter system set sga_target=1600m scope=spfile;;

System altered.

Issue resolved.