rem $TOOLS/dbousers.sql
rem
rem Prints a report of all current users in the current database
rem
rem Last Change 12/04/96 by Brian Lomasky
rem
set echo off
set feedback off
set termout off
set verify off
column adate new_value now
column name new_value dbname
select to_char(sysdate, 'MM/DD/YY') adate, name from v$database;
set feedback on
set pagesize 62
set concat +
spool dbousers.lst
set concat .
column username format a12 heading "Oracle|Username"
column osuser format a8 heading "O/S  |Username"
column sid format 9999 heading "Sid"
column status format a5 heading "Stat"
column type format a4 heading "Type"
column process format a6 heading "O/S|Pid"
column terminal format a10 heading "Term"
column lockwait format a4 heading "Lock|Wait"
column command format a12 heading "Command"
ttitle left &now center 'DBOUSERS - Database Users for &dbname' skip 2
select s.username, osuser, sid,
decode(status,'ACTIVE','Act','INACTIVE','Inact','KILLED','Kill',status) status,
decode(type,'BACKGROUND','Back','USER','User',type) type, p.spid process,
s.terminal, decode(lockwait,'','','Y') lockwait,
decode(command,0,'',1,'Create Table',2,'Insert',3,'Select',4,'Create Clust',
	5,'Alter Clustr',6,'Update',7,'Delete',8,'Drop',9,'Create Index',
	10,'Drop Index',11,'Alter Index',12,'Drop Table',15,'Alter Table',
	17,'Grant',18,'Revoke',19,'Create Syn',20,'Drop Synonym',
	21,'Create View',22,'Drop View',26,'Lock Table',27,'nop',28,'Rename',
	29,'Comment',30,'Audit',31,'Noaudit',32,'Cre Ext Data',
	33,'Drop Ext Dat',34,'Create Data',35,'Alter Data',36,'Cre Roll Seg',
	37,'Alt Roll Seg',38,'Drp Roll Seg',39,'Cre Tablesp',40,'Alt Tablesp',
	41,'Drop Tablesp',42,'Alt Session',43,'Alter User',44,'Commit',
	45,'Rollback',46,'Savepoint',47,'PL/SQL',to_char(command)) command
from v$session s, v$process p
where s.paddr = p.addr
order by type desc, username, osuser, sid;
spool off
set termout on
exit