--准备数据 create table ob as select * from dba_objects; insert into ob select * from ob; insert into ob select * from ob; insert into ob select * from ob; insert into ob select * from ob; insert into ob select * from ob; select * from v$instance; select * from ob order by OWNER|| OBJECT_NAME|| SUBOBJECT_NAME|| OBJECT_ID|| DATA_OBJECT_ID|| OBJECT_TYPE|| STATUS|| TEMPORARY|| GENERATED|| SECONDARY|| NAMESPACE|| EDITION_NAME|| SHARING|| EDITIONABLE|| ORACLE_MAINTAINED|| APPLICATION|| DEFAULT_COLLATION|| DUPLICATED|| SHARDED|| CREATED_APPID|| CREATED_VSNID|| MODIFIED_APPID|| MODIFIED_VSNID; --查看 SQL devpoler 的连接进程号 [oracle@ol8rac1 ~]$ ps -ef|grep $ORACLE_SID|grep -v ora_|grep LOCAL grid 7206 1 0 08:55 ? 00:00:01 oracle+ASM1_asmb_orcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 7600 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 7610 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 7625 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 7792 1 0 08:56 ? 00:00:00 oracleorcl1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 136103 1 0 11:58 ? 00:00:00 oracleorcl1 (LOCAL=NO) oracle 255473 1 7 14:17 ? 00:00:00 oracleorcl1 (LOCAL=NO) pid是255473 为新增的连接 ； col program for a50 select program,TERMINAL,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024 PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX from V$PROCESS where SOSID= '255473'; 2 3 4 PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 5892.99707 7836.2002 960 115228.2 SYS@orcl1> select username,sid,serial#,paddr,status,machine,process,program from v$session where username is not null and PADDR='00000000873F07F8'; 2 USERNAME SID SERIAL# PADDR STATUS -------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------------- -------- MACHINE PROCESS PROGRAM ---------------------------------------------------------------- ------------------------ -------------------------------------------------- LIYS 154 564 00000000873F07F8 INACTIVE ll 25132 SQL Developer 在SQL Developer执行 select * from ob order by OWNER|| OBJECT_NAME|| SUBOBJECT_NAME|| OBJECT_ID|| DATA_OBJECT_ID|| OBJECT_TYPE|| STATUS|| TEMPORARY|| GENERATED|| SECONDARY|| NAMESPACE|| EDITION_NAME|| SHARING|| EDITIONABLE|| ORACLE_MAINTAINED|| APPLICATION|| DEFAULT_COLLATION|| DUPLICATED|| SHARDED|| CREATED_APPID|| CREATED_VSNID|| MODIFIED_APPID|| MODIFIED_VSNID; 查询当前的PGA_USED SYS@orcl1> l 1 select program,TERMINAL,spid,PGA_USED_MEM/1024 PGA_USED,PGA_ALLOC_MEM/1024 PGA_ALLOC,PGA_FREEABLE_MEM/1024 2 PGA_FREE,PGA_MAX_MEM/1024 PGA_MAX 3 from V$PROCESS 4* where SOSID= '255473' SYS@orcl1> / PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 45290.7939 45852.2002 0 45852.2002 SYS@orcl1> / PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 70378.0205 70940.2002 0 70940.2002 SYS@orcl1> / PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 81641.7627 82204.2002 0 82204.2002 PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 105193.224 105756.2 0 105756.2 SYS@orcl1> / PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 111337.153 111900.2 0 111900.2 SYS@orcl1> / PROGRAM TERMINAL SPID PGA_USED PGA_ALLOC PGA_FREE PGA_MAX -------------------------------------------------- ------------------------------ ------------------------ ---------- ---------- ---------- ---------- oracle@ol8rac1 UNKNOWN 255473 112382.427 113244.2 192 113244.2 --可以看到PGA_USED是在增加的，之后有降了下来。