Please help - how do I find connection information?

[jnair]

Hi,

How can I find which clients are connected to an Oracle Database at a given point of time? (I want to find the PIDs of OCI and SQLPlus clients connected to the DB) - is there an Ora table which stores this information?

Any input will be greatly appreciated.

Thanks and regards,
J.

 

[stevecal]

OK, here's a point of departure.

SELECT sid,serial#,status,server, user#, username FROM v$session;

will give you your basic details

and if you want to kill them off:

ALTER SYSTEM KILL SESSION (sid),(serial#);

If you need anything else please let me know.

 

[Nebuchednezzar]

Here's a script I use on Unix when I know the unix pid and want to find out what the oracle sid is...

select s.username, s.osuser, s.status, s.sid, s.serial#, p.pid
from v$session s, v$process p
where s.paddr = p.addr
and p.spid = &system_id

...and here's a script to find the unix pid from a given oracle sid...

select p.spid from v$process p, v$session s
where p.addr = s.paddr
and s.sid = &sid;


...and a cursor to help you find process-related info for a particular table name...

declare
v_sid v$session.sid%TYPE;
v_serial v$session.serial#%type;
v_osuser v$session.osuser%type;
v_id V$lock.id1%type;
v_pid v$process.spid%TYPE;
begin
select object_id into v_id from USER_OBJECTS where object_name = '&OBJECT_NAME';
select sid into v_sid from v$lock where id1 = v_id;
select serial#, osuser into v_serial, v_osuser from v$session where sid = v_sid;
select p.spid into v_pid from v$process p, v$session s
where p.addr = s.paddr
and s.sid = v_sid;
dbms_output.put_line('SID = ' || v_sid ||
' ' ||
'SERIAL# = ' || v_serial ||
' ' ||
'OSUSER = ' || v_osuser ||
' ' ||
'UNIX PROCESS ID = ' || v_pid);
end;
/

 

[jnair]

Hi,

Thank you very much indeed...

regards,
J.