11gR2 Enterprise Manager exhausts the shared pool, resulting in ora-04031
In short:
Trying to log into EM exhausts the SGA memory of the database, the shared pool in specific, resulting in ora-04031 errors. Administration of the database with EM is no longer possible as it fails to load.
In more detail:
I installed 11gR2 SE (no upgrade) on a Linux Redhat 5.4 x64 machine using automatic memory management (AMM). After installation EM is accessible by browser. I can add users, tablespaces, datafiles, schedule a default backup which completes successfull. I can bring EM down, bring the database down, even do a reboot of the machine and everything keeps working fine.
Now I do a datapump import of the schema/tables with our data, the import completes successfull. I can connect to the database with sqldeveloper and do a max query on our largest table. By using the max on a non-indexed column I can force a full table scan and after some thinking the database returns the result. But after a while EM stops working. The trace files indicate an ora-04031 on the shared pool. Basically bringing down the entire database, as sqldeveloper connections are now refused with ora-04031 as well.
Trying to log into EM exhausts the SGA memory of the database, the shared pool in specific, resulting in ora-04031 errors. Administration of the database with EM is no longer possible as it fails to load.
In more detail:
I installed 11gR2 SE (no upgrade) on a Linux Redhat 5.4 x64 machine using automatic memory management (AMM). After installation EM is accessible by browser. I can add users, tablespaces, datafiles, schedule a default backup which completes successfull. I can bring EM down, bring the database down, even do a reboot of the machine and everything keeps working fine.
Now I do a datapump import of the schema/tables with our data, the import completes successfull. I can connect to the database with sqldeveloper and do a max query on our largest table. By using the max on a non-indexed column I can force a full table scan and after some thinking the database returns the result. But after a while EM stops working. The trace files indicate an ora-04031 on the shared pool. Basically bringing down the entire database, as sqldeveloper connections are now refused with ora-04031 as well.
0