Here is a shell script that will list all the Oracle users that are logged in to all databases on your HP-UX machine.
#!/bin/ksh export ORAENV_ASK=NO ps -ef | grep -i oracle | grep "LOCAL=" | grep -v grep |cut -d"?" -f2- | cut - d"oracle" -f2- | cut -c6- | cut -d" " -f-1 | sort -u |while read ORACLE_SID do . oraenv # echo $ORACLE_SID sqlplus -s internal << EOF set verify off set feedback 0 set lines 133 set pages 0 column osuser format a18 col name noprint new_value dbname select name from v$database; select '&dbname' , s.osuser from v$session s, v$process p where s.paddr = p.addr and s.type != 'BACKGROUND' and s.osuser is not null and s.osuser !='oracle' and s.audsid != userenv('SESSIONID') and union all select '&dbname' , s.schemaname from v$session s, v$process p where s.paddr = p.addr and s.type != 'BACKGROUND' and s.osuser is not null and s.osuser ='oracle' and s.audsid != userenv('SESSIONID') ; exit EOF done
About the Author
James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.
For More Information
- What do you think about this tip? E-mail the Editor at
- firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in January 2002