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
'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
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
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.
ReplyDeleteI have read your blog its very attractive and impressive. I like it your blog.
DeleteJava 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
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..
ReplyDeleteSelenium Training in Chennai | QTP Training in Chennai
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
ReplyDeleteOracle Apps Training In Chennai
Oracle Training in chennai | Oracle D2K Training In chennai
ReplyDeleteThis 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..
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
ReplyDeleteLearning 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.
ReplyDeleteBest JAVA Training institute in Chennai | Best JAVA Training in Chennai | Hadoop training in chennai
Really awesome blog. Your blog is really useful for me.
ReplyDeleteThanks for sharing this informative blog. Keep update your blog.
Oracle Training In Chennai
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..
ReplyDeleteIf 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
ReplyDeleteWell Said, you have furnished the right information that will be useful to anyone at all time. Thanks for sharing your Ideas.
ReplyDeleteWeb Designing Course in Chennai | web designing training in chennai
Thanks for sharing this unique and informative content which provided me the required information.
ReplyDeleteJava Training in Chennai | JAVA Course in Chennai
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
ReplyDeleteThank you for sharing this kind of beneficial records,
oracle fusion procurement online training
oracle fusion procurement training
I just read your comment Very interesting to read this article. If you are looking for best Oracle fusion online coaching.
ReplyDeleteOracle fusion financials training
Hi,
ReplyDeletethis is very interesting topic.
Thanks for sharing such a nice topic.
oracle fusion SCM online training
This information is impressive.thanks for the sharing this post.
ReplyDeletecatia summer trainingin Noida | Android 6months Industrial Training in Noida | Python Training in Noida
This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
ReplyDeleteAndroid Training in Chennai
Ios Training in Chennai
You are Oracle connections post is useful for me. Great effort.
ReplyDeleteOracle courses | DBA course
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.
ReplyDeleteSelenium 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
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.
ReplyDeleteAir 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
Excellent post! keep sharing such a post
ReplyDeleteGuest posting sites
Education
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.
ReplyDeleteCCNA 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
good work done and keep update more.i like your information's and
ReplyDeletethat is very much useful for readers.
angularjs classes in bangalore
AngularJS Training in Ambattur
AngularJS Training in Guindy
You have put some really helpful information. Keep it up. I expect more post from you.
ReplyDeleteC and C++ institute | C Language Training in Chennai | C C++ Training in Chennai | C Language Training in Adyar | C Language Training in Velachery | C Language Training in Tambaram
Innovative thinking of you in this blog makes me very useful to learn.
ReplyDeletei need more info to learn so kindly update it.
core Java Training in Bangalore
Java Training in Chennai Anna Nagar
Java Institute in T nagar
Java Training in Sholinganallur
Thanks for sharing this information admin, it helps me to learn new things. Continue sharing more like this.
ReplyDeleteUiPath Training in Chennai
UiPath Training
UiPath course in Adyar
Blue Prism Training in Chennai
RPA Training in Chennai
Robotic Process Automation Training
Your blog is so inspiring for the young generations.thanks for sharing your information with us and please update more new ideas.
ReplyDeleteGerman Training Institutes in Vadapalani
german Training near me
german language coaching classes in bangalore
german learning courses in bangalore
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
ReplyDeleteyou will post again soon.
AWS Training in Nungambakkam
AWS Training in Mogappair
AWS training courses near me
AWS Training in Bangalore
Awesome Post. Your writing is very unique. The choice of words stood as an exemplary piece of work. Thanks of sharing.
ReplyDeleteHtml5 Training in Chennai
Html5 Courses in Chennai
Html5 Training
Html5 Course
Html5 Training Course
Html5 Training for Beginners
Best Html5 Course
Best Html5 Training
Awesome Post. The choice of words makes this post very unique. Great content. Thanks for sharing.
ReplyDeleteNode JS Training in Chennai
Node JS Course in Chennai
Node JS Advanced Training
Node JS Training Institute in chennai
Node JS Training Institutes in chennai
Node JS Course
This is an awesome post.Really very informative and creative contents. These concept is a good way to enhance the knowledge.I like it and help me to development very well.Thank you for this brief explanation and very nice information.Well, got a good knowledge.
ReplyDeleteBig Data Analytics Courses in Chennai
Big Data Analytics Training in Chennai
Python Training in Chennai
Java Training in Chennai
Software Testing Training in Chennai
Big Data Analytics Training in Porur
Big Data Analytics Training in Adyar