Code Listing 1: Display UGA and PGA memory utilization.

CREATE OR REPLACE PROCEDURE show_memory
IS
   CURSOR mem_cur
   IS
      SELECT nm.NAME, st.VALUE
        FROM SYS.v_$session se, SYS.v_$sesstat st, SYS.v_$statname nm
       WHERE se.audsid = USERENV ('SESSIONID')
         AND st.statistic# = nm.statistic#
         AND se.SID = st.SID
         AND nm.NAME IN ('session uga memory', 'session pga memory');
BEGIN
   FOR rec IN mem_cur
   LOOP
      DBMS_OUTPUT.put_line (rec.NAME || ': ' || TO_CHAR (rec.VALUE));
   END LOOP;
END show_memory;