oracle connection的相关信息

oracle connection的相关信息

查看有哪些用户连接

select s.osuser os_user_name, decode(sign(48 - command), 1, to_char(command),

　　'Action Code #' || to_char(command) ) action, p.program oracle_process,

　　status session_status, s.terminal terminal, s.program program,

　　s.username user_name, &nbs

　　p; s.fixed_

table_sequence activity_meter, '' query,

　　0 memory, 0 max_memory, 0 cpu_usage, s.sid, s.serial# serial_num

　　from v$session s, v$process p where s.paddr=p.addr and s.type = 'USER'

　　order by s.username, s.osuser

2）根据v.sid查看对应连接的资源占用等情况

select n.name,

　　v.value,

　　n.class,

　　n.statistic#

　　from v$statname n,

　　v$sesstat v

　　where v.sid = 71 and

　　v.statistic# = n.statistic#

　　order by n.class, n.statistic#

3）根据sid查看对应连接正在运行的sql

select /*+ PUSH_SUBQ */

　　command_type,

　　sql_text,

　　sharable_mem,

　　persistent_mem,

　　runtime_mem,

　　sorts,

　　version_count,

　　loaded_versions,

　　open_versions,

　　users_opening,

　　executions,

　　users_executing,

　　loads,

　　first_load_time,

　　invalidations,

　　parse_calls,

　　disk_reads,

　　buffer_gets,

　　rows_processed,

　　sysdate start_time,

　　sysdate finish_time,

　　'>' || address sql_address,

　　'N' status

　　from v$sqlarea

　　where address = (select sql_address from v$session where sid = 71)