Scripts:列出所有连接数据库的用户信息sess_users.sql
来源:互联网 发布:钢结构棚子算法 编辑:程序博客网 时间:2024/05/18 16:16
-- +----------------------------------------------------------------------------+
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : sess_users.sql |
-- | CLASS : Session Management |
-- | PURPOSE : List all currently connected users. This script is RAC enabled. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : User Sessions (All) |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a8 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'
COLUMN os_username FORMAT a18 HEADING 'O/S User'
COLUMN os_pid FORMAT a8 HEADING 'O/S PID'
COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC
COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC
COLUMN session_program FORMAT a40 HEADING 'Session Program' TRUNC
BREAK ON instance_name SKIP PAGE
SELECT
i.instance_name instance_name
, s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.terminal session_terminal
, s.machine session_machine
, s.program session_program
FROM
gv$session s
INNER JOIN gv$process p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
INNER JOIN gv$instance i ON (p.inst_id = i.inst_id)
ORDER BY
i.instance_name
, s.sid;
-- | Jeffrey M. Hunter |
-- | jhunter@idevelopment.info |
-- | www.idevelopment.info |
-- |----------------------------------------------------------------------------|
-- | Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved. |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle |
-- | FILE : sess_users.sql |
-- | CLASS : Session Management |
-- | PURPOSE : List all currently connected users. This script is RAC enabled. |
-- | NOTE : As with any code, ensure to test this script in a development |
-- | environment before attempting to run it in production. |
-- +----------------------------------------------------------------------------+
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : User Sessions (All) |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name FORMAT a8 HEADING 'Instance'
COLUMN sid FORMAT 999999 HEADING 'SID'
COLUMN serial_id FORMAT 99999999 HEADING 'Serial ID'
COLUMN session_status FORMAT a9 HEADING 'Status'
COLUMN oracle_username FORMAT a18 HEADING 'Oracle User'
COLUMN os_username FORMAT a18 HEADING 'O/S User'
COLUMN os_pid FORMAT a8 HEADING 'O/S PID'
COLUMN session_terminal FORMAT a10 HEADING 'Terminal' TRUNC
COLUMN session_machine FORMAT a30 HEADING 'Machine' TRUNC
COLUMN session_program FORMAT a40 HEADING 'Session Program' TRUNC
BREAK ON instance_name SKIP PAGE
SELECT
i.instance_name instance_name
, s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.terminal session_terminal
, s.machine session_machine
, s.program session_program
FROM
gv$session s
INNER JOIN gv$process p ON (s.paddr = p.addr AND s.inst_id = p.inst_id)
INNER JOIN gv$instance i ON (p.inst_id = i.inst_id)
ORDER BY
i.instance_name
, s.sid;
0 0
- Scripts:列出所有连接数据库的用户信息sess_users.sql
- Scripts:列出用户信息的脚本sec_users.sql
- Scripts:查询数据库中所有的表dba_tables_all.sql
- Scripts:显示数据库所有锁的脚本locks_blocking.sql
- 查看连接到SQL Server数据库的用户信息
- Scripts:列出角色信息的脚本sec_roles.sql
- Scripts:报告数据库中所有的数据文件情况(包括临时表空间)dba_files.sql
- Scripts:报告数据库中所有已注册组件的汇总dba_registry.sql
- 列出所有重复记录的SQL
- 列出数据库中所有的表名
- 列出数据库中所有的表名
- Scripts:报告数据库中所有数据文件使用情况dba_files_all.sql
- 列出当前数据库中所有表的大小的SQL语句
- 求一条列出SQL SERVER2000数据库中所有存储过程名的命令
- 用NBear的生成实体设计代码工具连接ORACLE数据库时,只列出了数据库中所有的视图,没有列出表的问题
- 列出SQL SERVER数据库中所有表及字段信息
- 列出SQL SERVER数据库中所有表及字段信息
- SQL清除某个数据库的所有数据库连接
- OpenGL ES入门详解
- unityvs每次都要输入license问题
- 【Linux驱动】文件描述符以及相关知识
- 《构建跨平台APP:jQuery Mobile移动应用实战》
- 一个Java程序员应该掌握的10项技能
- Scripts:列出所有连接数据库的用户信息sess_users.sql
- OpenCV下HSV分量的值
- 安装JDK出现"Windows Installer程序包有问题,此安装需要的DLL不能运行"解决方法
- 深入理解计算机系统之旅(二)信息在计算机中的表示和处理
- ArrayList遍历的4种方法
- Android 使用意图播放本地视频
- NIO--SocketChannel发送HTTP请求
- Scripts:找出后台trace文件的脚本sess_user_trace_file_location.sql
- Linux对外连接端口数限制