SQL Script & Oracle

[saw15]

Good day ,

I would like to use the following script to pull all users logged in that are running jobs from Seagate Info.

Appears that this script should work, but its not. Any help is appreciated. I do / would run it via SQL Plus.

select rpad(username,8)"User",
to_char(logon_time,'MM/DD HH24:MI') "Start Time",
rpad(machine,20) "Info server", status
from v$session
where machine like 'EHS%'
order by 3,2

/*Get count of and list of users on each info server*/
set pagesize 40
break on "Machine" skip 1
compute count of "Ora User" on "Machine"
select rpad(machine,20) "Machine",
rpad(a.username,8) "Ora User",
rpad(to_char(logon_time, 'MM/DD-HH24:MI:SS'),11) "Login Time",
rpad(a.status,1,'_') "S"
from v$session a, v$process b
where a.paddr = b.addr
and machine like 'EHS_PN%'
and a.program like '%Seagate%'
order by 1,2;

Thanks

 

[jee]

What is happening when you run this? I tried it with some minor changes (to machine and program constants) it seemed to work fine. Are you getting an error message or is it just returning the wrong results (and what are those results)?

 

[saw15]

No data set is returned, comes back empty. When I remove the program reference to Seagate, everthing thing is returned (way to much).

It appears, using oracle 8.1.7 that this field program is no longer populated they way it was (wild guess). So I was looking for something other than program, or maybe someone / thing has had the same issue.

Thanks

 

[jee]

Oh yes, I forgot about that - I have run into that problem myself and had to change a stored procedure or two that referenced the PROGRAM column. It seems that in most cases the PROGRAM column is NULL under 8.1.7.

There are also PROCESS and MODULE columns in V_$SESSION that might help. I don't know what platform you are on but on Windows NT/2000 the PROCESS column holds a number of the format nn:nn (123:4331, for example). The part before the colon is the process ID of the connected task. MODULE holds some sort of program name, but it may be NULL sometimes too. Anyway, maybe one of these could be used.