Check oracle number of connections

To check max number of connections that is allowed for an Oracle database (http://stackoverflow.com/questions/162255/how-to-check-the-maximum-number-of-allowed-connections-to-an-oracle-database)

SELECT
'Currently, '
|| (SELECT COUNT(*) FROM V$SESSION)
|| ' out of '
|| VP.VALUE
|| ' connections are used.' AS USAGE_MESSAGE
FROM
V$PARAMETER VP
WHERE VP.NAME = 'sessions'

To monitor number of connections in Oracle (http://decipherinfosys.wordpress.com/2007/02/10/monitoring-number-of-connections-in-oracle/)

SELECT s.username AS username,

  (

  CASE

    WHEN grouping(s.machine) = 1

    THEN '**** All Machines ****'

    ELSE s.machine

  END)     AS machine,

  COUNT(*) AS session_count

FROM v$session s,

  v$process p

WHERE s.paddr   = p.addr

AND s.username IS NOT NULL

GROUP BY rollup (s.username, s.machine)

ORDER BY s.username,

  s.machine;

To see what SQL users are running on the Oracle database (thanks to http://www.dba-oracle.com/concepts/query_active_users_v$session.htm):

SELECT a.sid,

  a.serial#,

  a.username,

  b.sql_text

FROM v$session a,

  v$sqlarea b

WHERE a.sql_address=b.address;

To see what sessions are blocking other sessions (thanks to http://www.dba-oracle.com/concepts/query_active_users_v$session.htm):

SELECT blocking_session,

  sid,

  serial#,

  wait_class,

  seconds_in_wait

FROM v$session

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session;

Another good articles about session stats can be found at:
http://vsbabu.org/oracle/sect04.html
http://www.shutdownabort.com/dbaqueries/Administration_Session.php

And finally, when "bad" sessions are found, we can kill them using (http://www.oracle-base.com/articles/misc/KillingOracleSessions.php):

ALTER SYSTEM KILL SESSION 'sid,serial#';

Another good queries from http://stackoverflow.com/questions/622289/how-to-check-oracle-database-for-long-running-queries

This one shows SQL that is currently "ACTIVE"

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece
/

This shows locks

select
  object_name, 
  object_type, 
  session_id, 
  type,   -- Type or system/user lock
  lmode,     -- lock mode in which session holds lock
  request, 
  block, 
  ctime   -- Time since current mode was granted
from
  v$locked_object, all_objects, v$lock
where
  v$locked_object.object_id = all_objects.object_id AND
  v$lock.id1 = all_objects.object_id AND
  v$lock.sid = v$locked_object.session_id
order by
  session_id, ctime desc, object_name
/

This is a good one for finding long operations (e.g. full table scans). If it is because of lots of short operations, nothing will show up.

COLUMN percent FORMAT 999.99 

SELECT sid, to_char(start_time,'hh24:mi:ss') stime, 
message,( sofar/totalwork)* 100 percent 
FROM v$session_longops
WHERE sofar/totalwork < 1
/

To be continued... More documentation can be found at: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm