先建立视图sql_summary，以备后面脚本调用：

rem FUNCTION: Creates summary of v_$sqlarea and dba_users for use in

rem            sqlmem.sql and sqlsummanry.sql reports

rem

rem

create or replace view sql_summary as

            select username,

                   sharable_mem,

                   persistent_mem,

                   runtime_mem

            from

                   sys.v_$sqlarea a, dba_users b

            where

                        a.parsing_user_id = b.user_id;

再根据该视图检索每个用户的内存使用信息

rem

rem FUNCTION: Generate a summary of SQL Area Memory Usage

rem            uses the sql_summary view

rem

rem

rem sqlsummary.sql

rem

column areas                                        heading Used|Areas

column sharable format 999,999,999    heading Shared|Bytes

column persistent format 999,999,999  heading Persistent|Bytes

column runtime format 999,999,999      heading Runtime|Bytes

column username format a15                 heading "User"

column mem_sum format 999,999,999 heading Mem/Sum

start title80 "Users SQL Area Memory Use"

--spool rep_out&dbsqlsum

set pages 59 lines 80

break on report

compute sum of sharable on report

compute sum of persistent on report

compute sum of runtime on report

compute sum of mem_sum on report

select

username,

sum(sharable_mem) Sharable,

sum( persistent_mem) Persistent,

sum( runtime_mem) Runtime ,

count(*) Areas,

sum(sharable_mem+persistent_mem+runtime_mem) Mem_sum

from

sql_summary

group by username

order by 2;

--spool off

pause Press enter to continue

clear columns

clear breaks

set pages 22 lines 80

ttitle off

执行结果如下：

Date: 02/07/06                                                   Page:   1

Time: 01:45 AM              Users SQL Area Memory Use            SYS

                                 SDN09T database

                      Shared          Persistent         Runtime       Used

User                   Bytes              Bytes             Bytes      Areas      Mem/Sum

--------------- ------------ ------------ ------------ ---------- ------------

MKTS                   6,968          904          360                       1        8,232

NAI1957                7,496          904          360                      1        8,760

ECOM                  24,029        1,368        8,688                   1       34,085

U431322               39,270        2,032       13,360                  2       54,662

SDN_ACCT       375,547       12,560       82,288                12      470,395

VBM_ACCT       802,090       35,168      221,464               33    1,058,722

NAI0099           4,072,575       63,472      788,384              53    4,924,431

FUSNLWD       5,546,142       72,024    2,154,160             60    7,772,326

SYS                  10,210,666      280,920    2,534,384           236   13,025,970

UNIVERSE40   63,459,048    3,491,552   17,656,696       3508   84,607,296

                ------------ ------------ ------------            ------------

sum               84,543,831    3,960,904   23,460,144             111,964,879

10 rows selected.

SHARABLE_MEM: Amount of shared memory used by a cursor

PERSISTENT_MEM: Fixed amount of memory used for the lifetime of an open cursor

RUNTIME_MEM: Fixed amount of memory required during execution of a cursor