info

Useful Oracle Queries

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'