info.michael-simons.eu

Use the following class to view and modify the settings of the embedded JVM inside an Oracle database. The class has been tested on 10g and 11g, memory settings and information are in Megabyte.

SET DEFINE OFF
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "MemoryStatistics" AS
import oracle.aurora.vm.OracleRuntime;

public class MemoryStatistics {
    public final static int MB_MULTIPLICATOR = 1024*1024;

	public static String getStatistics() {
		final Runtime runtime = Runtime.getRuntime();
		
		final long totalMemory = runtime.totalMemory();
		final long maxMemory = runtime.maxMemory();
		final long usedMemory = totalMemory - runtime.freeMemory();

        return new StringBuffer()
        	.append(usedMemory/MB_MULTIPLICATOR)
        	.append(" / ")
        	.append(totalMemory/MB_MULTIPLICATOR)
        	.append(" MB (used / total), Maximum ")
        	.append(OracleRuntime.getMaxMemorySize()/MB_MULTIPLICATOR)
        	.append(", running on ")
        	.append(System.getProperty("java.version"))
        	.toString();
	}
    
    public static long getMaxMemorySize() {
        return OracleRuntime.getMaxMemorySize() / MB_MULTIPLICATOR;
    }
    
    public static void setMaxMemorySize(final long maxMemorySize) {
        OracleRuntime.setMaxMemorySize(maxMemorySize*MB_MULTIPLICATOR);
    }
}
/

SHO ERR;

CREATE OR REPLACE PROCEDURE p_set_max_memory_size(p_max_memory_size IN NUMBER) IS LANGUAGE JAVA
    NAME 'MemoryStatistics.setMaxMemorySize(long)';
/
 
CREATE OR REPLACE FUNCTION f_get_max_memory_size RETURN NUMBER IS LANGUAGE JAVA
    NAME 'MemoryStatistics.getMaxMemorySize() return java.lang.Long';
/
 
CREATE OR REPLACE FUNCTION f_get_memory_statistics RETURN VARCHAR2 IS LANGUAGE JAVA
    NAME 'MemoryStatistics.getStatistics() return java.lang.String';
/
 
SELECT f_get_max_memory_size() FROM dual;
 
SELECT f_get_memory_statistics() FROM dual;
 
BEGIN
    p_set_max_memory_size(512);
END;
/
 
SELECT f_get_memory_statistics() FROM dual;

The memory settings is persistent across sessions, at least with my current setup.