First Script for you

We have a fair turn about of oracle related people in the company, people come and people go... I stay. I am the senior DBA, and really, the other 2 "DBA's" are great people. But are only DBA's when I am on holidays or sick, otherwise they are application support people... and then my blackberry gets a LOT of use. We just hired a "mini-me" to become a DBA (ex-developer), and he is great - green when it comes to DBA work - but great and LOVES to read manuals. I am looking forward to when he is trained and an asset.

90% of our applications are oracle based, our IT manager believes heavily in the centralized mainframe type concept. I agree with it too. The vast majority of our equipment and databases reside in the one city and we simply have WAN connections to our apps, either client/server, Citrix, or web.

This means unfortunately, we have a few servers, but lots of databases. Our "big iron" is HP-UX servers with a smattering of Linux and the biggest waste of time around, Windows.

In all of the people coming and going, all "experts", in talking with other DBA's that I interact with, mostly contractors on certain projects, or the resident DBA for a vendor. I find it astonishing how many of them know of the V$SESSION view, but really don't know anything about it, what the things mean, or what good it is to them in conjunction with other tables.

This script works when properly called by a .sh or a .bat file, and is a very simple listing of who is in the database, what they are are some information to track them down.
The information it shows is great, and boom, you know who/what/when/where and you can find out why with other scripts (to come later)

col logon_time format a18
col osuser format a8
col type format a4 truncated
col machine format a14
col sid_ser format a10
col pid format 999999
col spid format a7
col program format a30 truncated
col schemaname format a8
col status format a11
set linesize 180
set pages 5000
spool /tmp/session8i.log
column host_name format a8
column archiver format a8
column log_switch_wait format a15
column instance_name format a8
column version format a12
column shutdown_pending format a16
column username format a10
column LOGON_TIME format a16
column osuser format a10 truncated
column machine format a11 truncated
column status format a10 truncated
column program format a8 truncated
column unixpid format a8 truncated

set pagesize 55
set lines 132
select
instance_name, host_name, version,
to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') STARTUP_TIME
,(select
SUBSTR(TRUNC((SYSDATE-logon_time))' Days :'TRUNC(((SYSDATE-logon_time)-TRUNC((SYSDATE-logon_time)))*24)' hours:'TR
UNC(((((
SYSDATE-logon_time)-TRUNC((SYSDATE-logon_time)))*24)-TRUNC(((SYSDATE-logon_time)-TRUNC((SYSDATE-logon_time)))*24))*60)' mi
ns',0,25
)
"UPTIME"
FROM sys.v_$session a
where sid=1 /* this is PMON */
) "UPTIME"
, status
, database_status
,archiver
,logins
from v$instance;

set head off
set pagesize 5000

prompt Type OSUser Oracle User Machine Login SID,SERIAL UnixPID Program Status
prompt ---- -------- ---------- -------------- --------------- ---------- ------- ------- ------

select s.type
, upper(nvl(s.osuser,'JOB')) osuser
, s.username
--, s.machine
,trim(substr(s.machine,INSTR(s.machine,'\')+1,LENGTH(s.machine))) machine
, trim(TO_CHAR(s.logon_time,'DD-MON HH24:MI:SS')) "LOGON_TIME"
, TO_CHAR(s.sid)','TO_CHAR(s.serial#) sid_ser
, trim(substr(nvl(replace(server,' '),'D'),0,1)' 'p.spid) unixpid
, upper(trim(s.program)) program
, s.status
from v$process p, v$session s
where p.addr=s.paddr
order by decode(S.TYPE,'BACKGROUND',0,'USER',1),decode(s.osuser,null,'XXX',s.status) desc,server,decode(s.osuser,'oracle',0,
null,-10,2),logon_Time
;

prompt to kill a session, as sys execute this command:
prompt alter system kill session 'SID,SERIAL'
rem prompt to turn trace on a session, as sys execute this command:
rem prompt exec sys.dbms_system.set_sql_trace_in_session(SID,SERIAL,true);
prompt ---------------------------------

I hope that is readable.
I use this many times a day.