Oracle DB：查看当前数据库活动会话的相关信息
以下SQL查询当前连入Oracle数据库的会话的详细信息。
Command is：
—— —— —— —— ——
select
a.program,a.osuser,a.type,a.sid,a.serial#,a.paddr,a.username,a.command,a.status,a.schema#,a.schemaname,
b.TEMPORARY_TABLESPACE,b.username
from
v$session a,dba_users b
where
a.schemaname = b.username;
—— —— —— —— ——
Log：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
SQL> set pagesize 100 SQL> col program for a35 SQL> col username for a12 SQL> col osuser for a14 SQL> set linesize 300 SQL> SQL> select a.program,a.osuser,a.type,a.sid,a.serial#,a.paddr,a.username,a.command,a.status,a.schema#,a.schemaname,b.TEMPORARY_TABLESPACE,b.username from v$session a,dba_users b where a.schemaname = b.username; PROGRAM OSUSER TYPE SID SERIAL# PADDR USERNAME COMMAND STATUS SCHEMA# SCHEMANAME TEMPORARY_TABLESPACE USERNAME ----------------------------------- -------------- ---------- ---------- ---------- ---------------- ------------ ---------- -------- ---------- ------------------------------ ------------------------------ ------------ oracle@misrep1 (PMON) oracle BACKGROUND 555 1 00000000BF6AA628 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (DIAG) oracle BACKGROUND 554 1 00000000BF6AAE20 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (PSP0) oracle BACKGROUND 553 1 00000000BF6AB618 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (LMON) oracle BACKGROUND 552 1 00000000BF6ABE10 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (LMD0) oracle BACKGROUND 551 1 00000000BF6AC608 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (LMS0) oracle BACKGROUND 550 1 00000000BF6ACE00 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (LMS1) oracle BACKGROUND 548 1 00000000BF6AD5F8 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (MMAN) oracle BACKGROUND 546 1 00000000BF6ADDF0 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (DBW0) oracle BACKGROUND 545 1 00000000BF6AE5E8 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (LGWR) oracle BACKGROUND 544 1 00000000BF6AEDE0 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (CKPT) oracle BACKGROUND 543 1 00000000BF6AF5D8 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (RECO) oracle BACKGROUND 542 1 00000000BF6B05C8 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (CJQ0) oracle BACKGROUND 541 1 00000000BF6B0DC0 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (MMON) oracle BACKGROUND 540 1 00000000BF6B15B8 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (MMNL) oracle BACKGROUND 539 1 00000000BF6B1DB0 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (SMON) oracle BACKGROUND 538 1 00000000BF6AFDD0 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (LCK0) oracle BACKGROUND 537 1 00000000BF6B25A8 0 ACTIVE 0 SYS TEMP SYS racgimon@misrep1 (TNS V1-V3) oracle USER 535 18 00000000BF6B2DA0 SYS 0 INACTIVE 0 SYS TEMP SYS oracle@misrep1 (QMNC) oracle BACKGROUND 533 7 00000000BF6B3598 0 ACTIVE 0 SYS TEMP SYS racgimon@misrep1 (TNS V1-V3) oracle USER 530 8 00000000BF6B4588 SYS 0 ACTIVE 0 SYS TEMP SYS racgimon@misrep1 (TNS V1-V3) oracle USER 529 14 00000000BF6B5D70 SYS 0 INACTIVE 0 SYS TEMP SYS oracle@misrep1 (q000) oracle BACKGROUND 527 1 00000000BF6B4D80 0 ACTIVE 0 SYS TEMP SYS oracle@misrep1 (q001) oracle BACKGROUND 525 1 00000000BF6B5578 0 ACTIVE 0 SYS TEMP SYS sqlplus@misrep1 (TNS V1-V3) oracle USER 522 64452 00000000BF6B6D60 SYS 3 ACTIVE 0 SYS TEMP SYS SQL Developer adamhuan USER 521 4789 00000000BF6B6568 SYS 0 INACTIVE 0 SYS TEMP SYS rman@misrep1 (TNS V1-V3) oracle USER 519 1164 00000000BF6B8548 SYS 0 INACTIVE 0 SYS TEMP SYS rman@misrep1 (TNS V1-V3) oracle USER 518 4028 00000000BF6B7558 SYS 0 INACTIVE 0 SYS TEMP SYS racgimon@misrep1 (TNS V1-V3) oracle USER 517 13992 00000000BF6B7D50 SYS 47 INACTIVE 0 SYS TEMP SYS 28 rows selected. SQL> |
——————————————————————————
Ending。
比方说，要查看哪些用户是用PL/SQL Developer登录的Oracle实例，可以用下面的语句筛选：
set pagesize 100
set linesize 400
col TEMPORARY_TABLESPACE for a8
col program for a14
col username for a12
col osuser for a14
col schemaname for a9
col machine for a23
select
a.program,a.osuser,a.machine,a.type,a.sid,a.serial#,a.paddr,a.username,a.command,a.status,a.schema#,a.schemaname,
b.TEMPORARY_TABLESPACE,b.username
from
v$session a,dba_users b
where
a.schemaname = b.username
and
a.program=’plsqldev.exe’;
查询结果：
——————
Done。