--  FILE:   sess_info.sql
--
--  AUTHOR: Andy Rivenes, arivenes@appsdba.com, www.appsdba.com
--          Copyright (C) 1998 AppsDBA Consulting. All Rights Reserved.
--  DATE:   11/18/1998
--
--  DESCRIPTION:
--          Query to display session information.
--          
--  REQUIREENTS:
--
--  MODIFICATIONS:
--
--          11/23/1998, A. Rivenes, Added join to v$process for OS user
--                                  and process id.
--
-- 
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation, either version 3 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program.  If not, see <http://www.gnu.org/licenses/>.
-- 
--
SET PAGESIZE 9999;
SET LINESIZE 120;
COLUMN usr       HEADING 'User Name'        FORMAT A20;
COLUMN sid       HEADING 'Oracle|Process'   FORMAT 999999;
COLUMN status    HEADING 'Status'           FORMAT A8;
COLUMN server    HEADING 'Server'           FORMAT A9;
COLUMN schema    HEADING 'DB User'          FORMAT A30;
COLUMN sosusr    HEADING 'Server|OS User'   FORMAT A15;
COLUMN spid      HEADING 'Server|Process'   FORMAT 9999999;
COLUMN cosusr    HEADING 'Client|OS User'   FORMAT A15;
COLUMN process   HEADING 'Client|Process'   FORMAT A9;
COLUMN machine   HEADING 'Client|Machine'   FORMAT A20;
COLUMN terminal  HEADING 'Terminal'         FORMAT A10;
COLUMN program   HEADING 'Program'          FORMAT A40;
SELECT a.username usr,
       a.sid sid,
       a.status status,
       a.server server,
       a.schemaname schema,
       b.username sosusr,
       b.spid spid,
       a.osuser cosusr,
       a.process process,
       a.machine machine,
       a.terminal terminal,
       a.program program
  FROM v$session a,
       v$process b
 WHERE a.type != 'BACKGROUND'
   AND a.paddr = b.addr
 ORDER BY a.status DESC
/