Friday, May 9, 2008

Script to Get OS user name with terminal name

REM: Script to Get Os user name with terminal name
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

SELECT
DBA_USERS.USERNAME USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED') STATUS,
NVL(OSUSER, '-') OSUSER,
NVL(TERMINAL,'-') TERMINAL,
SUM(DECODE(V$SESSION.USERNAME, NULL, 0,1)) SESSIONS
FROM
DBA_USERS, V$SESSION
WHERE DBA_USERS.USERNAME = V$SESSION.USERNAME (+)
GROUP BY
DBA_USERS.USERNAME,
DECODE(V$SESSION.USERNAME, NULL, 'NOT CONNECTED', 'CONNECTED'),
OSUSER,
TERMINAL
ORDER BY 1 ;

2 comments:

  1. This is an excellent script, especially to track users in a database catering to multiple users from different machines. Many thanks for sharing. To view just the connected users, you can modify the WHERE clause as:
    WHERE dba_users.username = v$session.username(+) AND v$session.username IS NOT NULL

    ReplyDelete

Thanks for you valuable comments !