Wednesday, October 13, 2010

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;


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

18 comments:

  1. Oracle is one of the most traditional and promising CRM used for maintain their customers. If you want to know more about this crm and this features, reach us FITA. Rated as No.1 Oracle Training Institutes in Chennai.

    ReplyDelete
    Replies
    1. I have read your blog its very attractive and impressive. I like it your blog.

      Java Training in Chennai Core Java Training in Chennai Core Java Training in Chennai

      Java Online Training Java Online Training Core Java 8 Training in Chennai Core java 8 online training JavaEE Training in Chennai Java EE Training in Chennai

      Delete
  2. This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..
    Selenium Training in Chennai | QTP Training in Chennai

    ReplyDelete
  3. Thanks for Information Oracle Apps Technical is a collection of a bunch of collected applications like accounts payables, purchasing, inventory, accounts receivables, human resources, order management, general ledger and fixed assets, etc which have its own functionality for serving the business
    Oracle Apps Training In Chennai

    ReplyDelete
  4. Oracle Training in chennai | Oracle D2K Training In chennai
    This information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..


    ReplyDelete
  5. Excellent post!!! Java is most popular and efficient programming language available in the market today. It helps developers to create stunning desktop/web applications loaded with stunning functionalities. J2EE Training in Chennai | JAVA Training in Chennai

    ReplyDelete
  6. Learning new technolgoy would help oneself at hard part of their career. And staying updated is the only way to survive in current position. Your content tells the same. Thanks for sharing this information in here. Keep blogging like this.

    Best JAVA Training institute in Chennai | Best JAVA Training in Chennai | Hadoop training in chennai

    ReplyDelete
  7. Really awesome blog. Your blog is really useful for me.
    Thanks for sharing this informative blog. Keep update your blog.
    Oracle Training In Chennai

    ReplyDelete
  8. Best Java Training Institute In ChennaiThis information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    ReplyDelete
  9. If you are using Oracle Cluster ware 10gR2 or above for RAC or just for a single instance using ASM, the Cluster ware automatically starts and stops the Oracle database instances and listeners, so the following procedures are not necessary. Where the Cluster ware is not being used, these methods allow you to automate the startup and shutdown of databases on Linux. Oracle Apps Online Training

    ReplyDelete
  10. Well Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
    Web Designing Course in Chennai | web designing training in chennai

    ReplyDelete
  11. Thanks for sharing this unique and informative content which provided me the required information.
    Java Training in Chennai | JAVA Course in Chennai

    ReplyDelete
  12. Thanks for posting this blog i, in fact, loved it and positioned up a few specific blogs approximately oracle........go to our net internet web page associated with oracle fusion procurement training
    Thank you for sharing this kind of beneficial records,
    oracle fusion procurement online training
    oracle fusion procurement training

    ReplyDelete
  13. I just read your comment Very interesting to read this article. If you are looking for best Oracle fusion online coaching.
    Oracle fusion financials training

    ReplyDelete
  14. Hi,
    this is very interesting topic.
    Thanks for sharing such a nice topic.
    oracle fusion SCM online training

    ReplyDelete
  15. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  16. You are Oracle connections post is useful for me. Great effort.
    Oracle courses | DBA course

    ReplyDelete