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

30 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
  17. Thank you for sharing such valuable information and tips. This can give insights and inspirations for us; very helpful and informative! Would love to see more updates from you in the future.
    Selenium training in Chennai
    Selenium Courses in Chennai
    ios developer training in chennai
    Best ios Training institute in Chennai
    iOS Course Chennai
    JAVA Training Institutes in Chennai
    Java Courses in Chennai

    ReplyDelete
  18. Outstanding blog thanks for sharing such wonderful blog with us ,after long time came across such knowlegeble blog. keep sharing such informative blog with us.
    Air Hostess Training in Chennai | Air Hostess Training Institute in Chennai | Air Hostess Academy in Chennai | Air Hostess Course in Chennai | Air Hostess Institute in Chennai

    ReplyDelete
  19. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    CCNA Training in Chennai
    DevOps Training in Chennai
    Best devOps Training in Chennai
    DevOps foundation certificate
    Best CCNA Training Institute in Chennai
    CCNA certification in Chennai

    ReplyDelete
  20. good work done and keep update more.i like your information's and
    that is very much useful for readers.
    angularjs classes in bangalore
    AngularJS Training in Ambattur
    AngularJS Training in Guindy

    ReplyDelete
  21. Your blog is so inspiring for the young generations.thanks for sharing your information with us and please update more new ideas.
    German Training Institutes in Vadapalani
    german Training near me
    german language coaching classes in bangalore
    german learning courses in bangalore

    ReplyDelete
  22. Thanks for the great post on your blog, it really gives me an insight on this topic.I must thank you for this informative read. I hope
    you will post again soon.
    AWS Training in Nungambakkam
    AWS Training in Mogappair
    AWS training courses near me
    AWS Training in Bangalore

    ReplyDelete