Grant Select on all tables Oracle

Here is a SQL Script that will generate a script to allow you to grant select on all the tables of a specified owner.

select 'grant select on '||owner||'.'||table_name||' to other_user;' from all_tables where owner = 'some_user';

The resulting script can be executed to do a mass grant on all tables.

Tracking down Oracle Sessions

This query will show the current users and the OS PID…you can use this to then run a top command to watch the session.

ACTIVE users

select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
, s.status
, s.last_call_et
, sq.physical_read_bytes
, sq.physical_write_bytes
, sq.sql_text
from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
, v$process p
Where s.paddr = p.addr
and s.status = 'ACTIVE'
and s.osuser <> 'oracle'
order by to_number(p.spid)

ALL Users (except oracle)

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and s.osuser <> 'oracle'
 order  by to_number(p.spid)

From there you can use the OS PID to run a top command to see what kind of resources the session is consuming

top -p PID

Or you can go the opposite way. Run a top command and then track down the Oracle Sessions using the PID

Find Active Users

select s.sid || ',' || s.serial# "SID/SERIAL"
 , s.username
 , s.osuser
 , p.spid "OS PID"
 , s.program
 , s.status
 , s.last_call_et
 , sq.physical_read_bytes
 , sq.physical_write_bytes
 , sq.sql_text
 from v$session s LEFT OUTER JOIN v$sql sq ON sq.sql_id = s.sql_id
 , v$process p
 Where s.paddr = p.addr
 and p.spid IN (22057, 28919, 21972)
 order  by to_number(p.spid)

Oracle Index Information

Some queries to help find out infomration about specific indexes.

This will show you the indexes for a specified table

select	i.index_name
,	i.tablespace_name
,	ceil(s.bytes / 1048576) "Size MB"
from 	dba_indexes i
,	dba_segments s
where 	i.index_name = s.segment_name
and 	table_name like '&table'
order 	by 2, 1

This will show you the columns of a specific index

select 	column_name
from 	dba_ind_columns
where 	index_name = '&index'
order 	by column_position

This will give you some extra details about the index

select index_name, num_rows, last_analyzed from dba_ind_statistics where table_name = 'Table Name';