this all comes with no guarantees and is primarily for my own use. use at your own risk.

List open sessions

select a.spid pid, b.sid sid, b.serial#, b.machine, b.username, b.osuser, b.program
from v$session b, v$process a
where b.paddr = a.addr and type='USER'
order by spid

Find a table via a field name

select * from user_tab_columns where column_name like '...'

List all tables

select * from user_objects where object_type = 'TABLE'

Get the source for a stored procedure

select text from user_source where name = '...' order by line

(spool a good option here when using SQL*Plus...)

List of stored procedures

select distinct name from user_source where type = 'PROCEDURE'