A connection is a network link connection to the database. A connection can have 0, one or many sessions, but the normal situation is only one session per connection.
The tasks in a session are executed by processes. But a process does not have to be dedicated to a specific session.
If you want to see the current active sessions, select the v$session table:
SQL> select s.sid, s.serial#, s.type, s.status, s.username, s.schemaname from v$session s
If you want to see the current processeses running in background, you have to search also in the v$process table:
SQL> select s.sid, s.serial#, p.spid, s.username, s.schemaname, s.program, s.terminal, s.osuser, s.status from v$session s join v$process p on s.paddr = p.addr where s.type != 'BACKGROUND';
The output will be something like this:
SID SERIAL# SPID USERNAME SCHEMANAME PROGRAM TERMINAL OSUSER STATUS --- ------- ----- --------- ------------ --------------------------- -------- ------- ----- 142 56088 8135 SYS SYS sqlplus@mydb02 (TNS V1-V3) pts/1 oracle ACTIVE 131 37655 7732 SYS SYS sqlplus@mydb02 (TNS V1-V3) pts/2 oracle INACTIVE 44 47015 7997 DBUSER DBDATA JDBC Thin Client unknown manager ACTIVE 15 48699 7999 DBUSER DBDATA JDBC Thin Client unknown manager KILLED [...]
If you want to kill a session just write use the command alter system kill session ‘sid,serial#’, for example:
SQL> alter system kill session '142,56088';
This will kill the session once it has finished the current ongoing transaction. If you want to stop it now, add the word immediate at the end:
SQL> alter system kill session '142,56088' immediate;
This will stop the ongoing transaction and will start the roll back of the changes made in that transaction. This can take a while if it has to move a lot of data, so the process will change its status to KILLED until the roll back finish, then it will dissappear.
Another form to kill a session is to terminate the PID for the running process. In linux, in the console you can write
$ kill -9 SPID
where SPID is the process id of the desired process that was shown in the previous table.