Tuesday 29 November 2016

Oracle session details and query to find out details

Hi DBA-Mates,
Many times we all faced issues like blocking session, High Temp usage and etc. which is related to session.
As we all knowing that every login requires a session. It specifies the maximum number of the sessions that can be created in the system. 
Actually, this parameter determines the maximum number of concurrent users in the system.
It can set this parameter explicitly by to a value almost closed to our maximum number of the concurrent users plus the number of background processes and approx 10% of the recursive sessions.

By default value for this parameter is as its minimum and values between 1 and the default do no trigger error. But always remember oracle ignores them and uses the default instead.

Always remember that the default values of ENQUEUE_RESOURCES and TRANSACTIONS parameters are also derived from SESSIONS. Therefore, if you increase the value of SESSIONS , you also should consider whether to adjust the values of above two parameters ENQUEUE_RESOURCES and TRANSACTIONS.
This sql query displays information about a session.
For this query, we should have SID number available.

column sid                                format 9999
column serial                             format 99999
column osuser   HEADING 'O/S|Username'    FORMAT a9
column username HEADING 'Oracle|Username' FORMAT a9
column program                            FORMAT a20

SELECT sid, serial#, osuser, username, status, logon_time, program
FROM v$session
WHERE sid like '&SID'
/

And some times, if the session is INACTIVE form session, we can kill the session.
In case of Active or Inactive Toad connections , SQL/PLSQL session can be killed but remember always if you are working on production, we must should follow some points like:
1. we should collect the details related to that particular session.
2. Inform the concern team.
3. Get approval.
4. Now you are ready to kill the session.

A session can be killed by below command:
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

But in RAC, its is different because in RAC servers we use gv$session. And to kill the session we should use below command:
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

If you want to kill the session Immediate because as we know this kill command will asks session to kill itself. But in any situation if you want to kill immediately, So better to use IMMEDIATE clause as shown below command.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Also from this below session query, we can get the information about the sessions.

It will ask for some values which can be escape if you are not knowing and hit enter.

Select ' Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||

       s.audsid||chr(10)|| '     DB User / OS User : '||s.username||

       '   /   '||s.osuser||chr(10)|| '    Machine - Terminal : '||

       s.machine||'  -  '|| s.terminal||chr(10)||

       '        OS Process Ids : '||

       s.process||' (Client)  '||p.spid||' (Server)'|| chr(10)||

       '   Client Program Name : '||s.program "Session Info"
  from v$process p,v$session s
 where p.addr = s.paddr
   and s.sid = nvl('&SID',s.sid)
   and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))
   and s.process = nvl('&Process',s.process)
   and p.spid = nvl('&spid',p.spid)
   and s.username = nvl('&username',s.username)
   and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))
   and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))
   and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',
       nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'
/
Hope you learned something new here. Please let us know for any concerns or suggestions either in comment box or Contact US. Remember we loved to listen from you.

Hope these will be useful:
How to Read and Write UTL file!!
EBS R12 Installation Steps
ORACLE DATABASE
Rebuild Context file Steps
Regards,
ora-data Team

107 comments:


  1. Dear User,

    Thank you for your observation and comment.
    We try to correct it and also please suggest if you found it here.

    Regards,
    ora-data Team

    ReplyDelete
  2. You really make it seem so easy with your presentation but I find this matter to be
    actually something that I think I would never understand.
    It seems too complicated and very broad for me. I am looking forward for
    your next post, I'll try to get the hang of it!

    ReplyDelete
  3. I must thank you for the efforts you have put in penning this site.
    I really hope to see the same high-grade content from you later on as well.
    In truth, your creative writing abilities has encouraged
    me to get my own blog now ;)

    ReplyDelete
  4. Fastidious replies in return of this query with solid
    arguments and describing all regarding that.

    ReplyDelete
  5. hello there and thank you for your information – I
    have certainly picked up anything new from right here.
    I did however expertise some technical issues using this web site, as I experienced to reload the web site a
    lot of times previous to I could get it to load correctly.

    I had been wondering if your hosting is OK? Not that I'm complaining, but
    slow loading instances times will very frequently affect your placement in google and can damage your quality score if ads
    and marketing with Adwords. Well I'm adding this RSS
    to my email and could look out for a lot more of your respective
    fascinating content. Ensure that you update this again soon.

    ReplyDelete
  6. Hi there, after reading this amazing post i am
    too happy to share my familiarity here with
    friends.

    ReplyDelete

  7. Dear User,

    I don't have much Idea on this but you can use google blogspot.
    Hope this may useful.

    Regards,
    ora-data Team

    ReplyDelete
  8. My coder is trying to persuade me to move to .net from
    PHP. I have always disliked the idea because of the expenses.
    But he's tryiong none the less. I've been using WordPress on a number of
    websites for about a year and am anxious about switching to another
    platform. I have heard fantastic things about blogengine.net.
    Is there a way I can transfer all my wordpress content into it?
    Any kind of help would be greatly appreciated!

    ReplyDelete
    Replies

    1. Dear User,

      I don't have much idea about it. I also need to check it.

      Regard,
      ora-data Team

      Delete

  9. Dear User,

    Thank you for your valuable feedback.

    Regards,
    ora-data Team

    ReplyDelete
  10. I'm truly enjoying the design and layout of your site.
    It's a very easy on the eyes which makes it much more
    enjoyable for me to come here and visit more often. Did
    you hire out a developer to create your theme? Great work!

    ReplyDelete
  11. I have been browsing on-line more than three hours
    nowadays, yet I never discovered any fascinating article like yours.
    It's lovely value enough for me. In my view, if all web owners and bloggers made good content
    as you did, the net might be much more useful than ever before.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you very much for such great words and feedback.
      We will try our best to continue Quality...

      Regards,
      ora-data Team

      Delete
  12. Dear User,

    Please follow us on Facebook : Our Facebook Page
    and google plus: Google Plus Page

    Regards,
    ora-data Team

    ReplyDelete
  13. First off I want to say superb blog! I had a quick
    question in which I'd like to ask if you do not mind.
    I was interested to know how you center yourself and clear your mind before writing.
    I've had trouble clearing my thoughts in getting my thoughts
    out there. I truly do take pleasure in writing however it
    just seems like the first 10 to 15 minutes are lost simply just trying to figure out how to begin. Any recommendations or hints?

    Thank you!

    ReplyDelete
    Replies
    1. Dear User,
      Thank you for your valuable feedback.
      It happens with everyone. Really believe me it takes time for me also to write in clear steps.
      That's why I write very less but clear steps and concepts. I don't simply copy and paste. Hope you got it .

      Regards,
      ora-data Team

      Delete
  14. Awesome blog! Do you have any suggestions for aspiring writers?
    I'm planning to start my own website soon but I'm
    a little lost on everything. Would you suggest starting with a free platform like Wordpress or go
    for a paid option? There are so many options out
    there that I'm completely overwhelmed ..
    Any ideas? Thank you!

    ReplyDelete
    Replies
    1. Dear User,
      If you want to expense money, please go ahead with paid domain.
      Sorry, I don't have wordpress experiences and I am not feeling any problem with blogspot.

      Regards,
      ora-data Team

      Delete
  15. Dear User,
    Thank you very much for your Great words...

    Regards,
    ora-data Team

    ReplyDelete
  16. І like what you guys аre usually up t᧐᧐.
    This kind of clever work and exposure! Keep up the eⲭcellent workѕ guys I've added you ɡuys
    to blogroll.
    her comment is here : How To Passѡord Protect Foⅼder

    ReplyDelete
  17. Hi, this is Dave,
    It will be great while you get a hold of this awesome "Internet Promotional Tools Crazy Offer and also net the 100% profit margin to your account.
    Lot's of software programs provided with life time updates. This really is wonderful a onetime fee of $9,95 only.
    Have a look at the website right now as well as get the most out of downloading any kind of the software you have already been looking forward to these days.
    See you inside

    ReplyDelete
  18. My partner and I absolutely love your blog and find nearly all
    of your post's to be just what I'm looking for.
    Do you offer guest writers to write content available
    for you? I wouldn't mind creating a post or elaborating on a number of the subjects you write regarding here.

    Again, awesome web log!

    ReplyDelete
    Replies
    1. Dear User,
      Thank you for your feedback.
      All are welcome here, if you have any suggestions or ideas please you can put either in Comment-box or Contact Us as shown top right side.

      Regards,
      ora-data Team

      Delete
  19. Hello there, You've done an incredible job. I will certainly digg it and personall suggest to
    my friends. I am sure they'll be benefited from this website.

    ReplyDelete
    Replies
    1. Dear User,
      Thank you very much for your valuable feedback.

      Regards,
      ora-data Team

      Delete
  20. Hi to every body, it's my first pay a quick visit of this blog; this webpage contains awesome and in fact fine stuff
    designed for readers.

    ReplyDelete
  21. Hi to every body, it's my first visit of this webpage; this weblog includes awesome and genuinely fine material designed
    for visitors.

    ReplyDelete
    Replies
    1. Dear User,
      Thank you very much for your interest and valuable feedback.

      Regards,
      ora-data Team

      Delete
  22. Hello friends, good article and nice urging commented at this place, I
    am really enjoying by these.

    ReplyDelete
  23. Very quickly this website will be famous among all blogging and site-building visitors,
    due to it's nice articles or reviews

    ReplyDelete
    Replies
    1. Dear User,
      Thank you very much for your valuable feedback.

      Regards,
      ora-data Team

      Delete
  24. I love it when folks come together and share thoughts.
    Great blog, stick with it!

    ReplyDelete
  25. Hi there terrific blog! Does running a blog such as this take a massive
    amount work? I've very little understanding of computer programming however I was hoping to start
    my own blog in the near future. Anyways, if you have any ideas or tips for
    new blog owners please share. I understand this is off subject nevertheless I simply needed to
    ask. Thanks a lot!

    ReplyDelete
  26. It's awesome to pay a visit this web page and reading the views of
    all mates concerning this post, while I am also keen of getting knowledge.

    ReplyDelete
    Replies
    1. Dear User,
      Thank you very much for your valuable feedback.

      Regards,
      ora-data Team

      Delete
  27. We are a bunch of volunteers aand opening a brand new scheme in our community.
    Your web site offered us with valuable information to work on. You have done a foprmidable task and our whole community will probably be grateful
    to you.

    ReplyDelete
  28. Nice post. I learn something new and challenging on blogs I
    stumbleupon on a daily basis. It's always interesting to read
    content from other writers and use something from other web sites.

    ReplyDelete
  29. This site was... how do I say it? Relevant!! Finally I
    have found something which helped me. Kudos!

    ReplyDelete
  30. Its like you learn my mind! You seem to understand a lot approximately this, like you wrote the ebook in it or
    something. I believe that you just can do with a few percent
    to drive the message home a little bit, but instead of that, that is great
    blog. An excellent read. I will definitely be back.

    ReplyDelete
  31. If you are going for finest contents like myself, simply pay a quick visit this site every day since it provides quality contents, thanks

    ReplyDelete
  32. Touche. Outstanding arguments. Keep up the good work.

    ReplyDelete
    Replies
    1. Dear User,
      Thank you very much for your kind feedback.

      Regards,
      ora-data Team

      Delete
  33. My relatives all the time say that I am killing my time here at web, however I
    know I am getting familiarity daily by reading thes pleasant
    articles.

    ReplyDelete
  34. Hello to every one, it's genuinely a fastidious for me to visit this web page, it consists of helpful Information.

    ReplyDelete
  35. Touche. Solid arguments. Keep up the great work.

    ReplyDelete
  36. Magnificent goods from you, man. I have keep in mind your
    stuff prior to and you're simply extremely great. I actually like
    what you've received right here, really like what you're saying and the way in which during which you
    are saying it. You make it enjoyable and you still take care of
    to stay it sensible. I can not wait to learn much more from you.
    This is really a tremendous website.

    ReplyDelete
  37. We absolutely love your blog and find most of your post's to be just what
    I'm looking for. can you offer guest writers to write content to suit your needs?
    I wouldn't mind writing a post or elaborating on some of the subjects you write about here.
    Again, awesome web log!

    ReplyDelete
  38. You can definitely see your expertise in the paintings you write.
    The sector hopes for even more passionate writers such as you who aren't
    afraid to say how they believe. At all times follow your heart.

    ReplyDelete
  39. Hi there to every single one, it's in fact a
    nice for me to pay a visit this web page, it includes important Information.

    ReplyDelete
  40. What's up, yup this post is truly good and I have learned lot of things from it
    concerning blogging. thanks.

    ReplyDelete
  41. This is a topic which is near to my heart... Best wishes! Where are your contact details though?

    ReplyDelete
  42. You're so interesting! I don't believe I have read something like that
    before. So great to discover somebody with a few original thoughts on this topic.
    Really.. thank you for starting this up. This site is one thing that is needed
    on the internet, someone with some originality!

    ReplyDelete
  43. Its like you read my mind! You appear to know a lot about this, like you wrote the book in it or something.

    I think that you could do with some pics to drive the message home a bit, but instead of that, this is excellent blog.
    A fantastic read. I will definitely be back.

    ReplyDelete
  44. I gotta favorite this website it seems invaluable handy.

    ReplyDelete
  45. Quality articles is the crucial to interest the users to visit the website, that's what this website is providing.

    ReplyDelete
  46. Only a smiling visitor here to share the love (:, btw outstanding design and
    style.

    ReplyDelete
  47. Howdy exceptional blog! Does running a blog like this take a massive amount work?

    I have very little knowledge of computer programming but I had been hoping to start my
    own blog in the near future. Anyways, if you have any ideas
    or techniques for new blog owners please share.
    I understand this is off topic however I just had to
    ask. Thank you!

    ReplyDelete
  48. Good post.Ne'er knew this, appreciate it for letting
    me know.

    ReplyDelete
  49. Thank you so much for providing individuals with remarkably marvellous
    possiblity to read in detail from this web site. It's usually very
    useful and also jam-packed with a lot of fun for me and my office colleagues to
    search your web site particularly 3 times in 7 days to see the latest stuff you have.
    And definitely, we are usually motivated for the staggering concepts served by you.
    Selected two areas in this post are clearly the most efficient I
    have had.

    ReplyDelete
  50. Appreciate it for this post, I am a big fan of this website would like to go on updated.

    ReplyDelete
  51. Thank you for sharing your info. I truly appreciate your efforts and I am waiting
    for your next post thanks once again.

    ReplyDelete
  52. Very clear site, thanks for this post.

    ReplyDelete
  53. Nice post. I was checking continuously this blog and
    I am impressed! Very useful info particularly
    the final section :) I care for such info much. I used to be seeking this certain information for a very long time.
    Thanks and best of luck.

    ReplyDelete
  54. As the admin of this web site is working, no uncertainty very
    quickly it will be renowned, due to its feature contents.

    ReplyDelete
  55. Howdy! I simply would like to offer you a big thumbs up for the great info you have here on this
    post. I will be coming back to your website for more soon.

    ReplyDelete
  56. I am constantly browsing online for tips that can help me.
    Thanks!

    ReplyDelete
  57. I don't even know how I ended up here, but I thought this post was great.
    I don't know who you are but definitely you're going to a famous blogger if
    you aren't already ;) Cheers!

    ReplyDelete
  58. We're a bunch of volunteers and starting a brand new scheme in our community.
    Your website provided us with useful information to paintings
    on. You've performed a formidable process
    and our whole group can be grateful to you.

    ReplyDelete
  59. Hi there, just became aware of your blog through
    Google, and found that it's really informative.
    I?m gonna watch out for brussels. I will appreciate if you continue this in future.
    Lots of people will be benefited from your writing.

    Cheers!

    ReplyDelete
  60. Hello.This article was really interesting, particularly because I was
    browsing for thoughts on this matter last couple of days.

    ReplyDelete
  61. You have brought up a very excellent points, regards for the post.

    ReplyDelete
  62. Informative article, exactly what I was looking for.

    ReplyDelete
  63. I pay a quick visit daily some sites and blogs to read articles or reviews, however this webpage
    gives feature based content.

    ReplyDelete
  64. Greate article. Keep writing such kind of information on your blog.
    Im really impressed by your blog.
    Hey there, You have performed an excellent job.
    I will definitely digg it and in my view recommend
    to my friends. I am sure they'll be benefited from
    this site.

    ReplyDelete
  65. thank you for this post, I am a big big fan of
    this site would like to go on updated.

    ReplyDelete
  66. WOW just what I was looking for. Came here by searching for heavy workout

    ReplyDelete
  67. I have to show thanks to the writer for rescuing me from such a matter.
    As a result of browsing throughout the search engines and seeing recommendations which
    are not powerful, I thought my entire life was over.
    Living without the presence of answers to the issues you've resolved by means
    of this guide is a serious case, as well as ones that might have adversely damaged my entire career if I hadn't
    come across your web page. That know-how and kindness in taking care of all the things was valuable.
    I don't know what I would've done if I hadn't come across such a thing like this.
    I'm able to at this point look ahead to my future. Thank you very much for
    the specialized and effective help. I won't hesitate to recommend your blog to anybody who needs tips about this issue.

    ReplyDelete
  68. I conceive this website contains some very excellent info for everyone :D.

    ReplyDelete
  69. I not to mention my buddies came taking note of the excellent tips found on the website then immediately came up with a horrible feeling
    I had not thanked the web site owner for those techniques.
    All of the men had been totally stimulated to read all of them and
    now have seriously been enjoying these things. We appreciate
    you turning out to be considerably considerate and for utilizing
    these kinds of superior useful guides millions of individuals are
    really needing to learn about. Our own honest apologies for not expressing
    gratitude to earlier.

    ReplyDelete
  70. Good article. I will be experiencing some of these issues as well..

    ReplyDelete
  71. Very nice style and design and excellent content material, very little else we need :D.

    ReplyDelete
  72. I do believe all of the concepts you've offered to your post.

    They are very convincing and can certainly work.
    Nonetheless, the posts are very short for newbies.
    May just you please lengthen them a bit from next time?
    Thank you for the post.

    ReplyDelete
  73. Hi, i think that i saw you visited my weblog thus i came to ?return the favor?.I am attempting to find things to enhance my web
    site!I suppose its ok to use some of your ideas!!

    ReplyDelete
  74. I do accept as true with all the ideas you've introduced to your post.
    They are really convincing and will definitely work. Still, the posts are very
    short for beginners. May just you please lengthen them a little from next time?
    Thank you for the post.

    ReplyDelete
  75. Hi, Neat post. There is a problem with your site in web explorer, may test this...
    IE nonetheless is the market chief and a huge element of other folks will omit your excellent
    writing because of this problem.

    ReplyDelete
  76. hi!,I like your writing so a lot! share we be
    in contact extra approximately your post on AOL?
    I require an expert on this area to solve my problem.
    May be that's you! Looking ahead to look you.

    ReplyDelete
  77. What i do not understood is if truth be told how you're no
    longer really much more smartly-liked than you may be right now.
    You are so intelligent. You already know therefore considerably on the subject of this topic,
    produced me in my opinion imagine it from a lot
    of numerous angles. Its like men and women are not interested unless it's one thing to accomplish with Woman gaga!

    Your own stuffs great. Always handle it up!

    ReplyDelete
  78. Thankfulness to my father who told me concerning this blog, this weblog is in fact amazing.

    ReplyDelete
  79. You are so interesting! I don't suppose I've truly read anything like this before.
    So wonderful to discover someone with unique thoughts on this subject matter.

    Really.. thanks for starting this up. This web site
    is something that is needed on the web, someone with a bit of
    originality!

    ReplyDelete
  80. I don't even know the way I ended up here, but I assumed this
    post used to be good. I do not realize who you are but certainly
    you're going to a well-known blogger in the
    event you are not already. Cheers!

    ReplyDelete
  81. Do you want to discover American English by viewing flicks?
    Are you searching for some pointers on just how to do it?
    If so, keep analysis, since you remain in the appropriate area.
    This write-up is for individuals that wish to do it!
    I'm mosting likely to show you 3 ideas that will aid you learn from movies.
    It's an extremely fun as well as reliable method to improve your language skills Right here are the ideas!


    1. Enjoy American movies without subtitles to boost your listening skills

    You can view flicks with or without English subtitles.

    I like watching flicks without captions. Why? Since it forces you
    to concentrate. It's pretty easy to see a film in a foreign language if you have captions.
    You just require to review, you do not require to pay attention closely.
    So, start seeing American films without captions and also you'll promptly enhance your listening abilities.

    ReplyDelete
  82. Hello mates, fastidious article and nice arguments commented here, I am actually enjoying by
    these.

    ReplyDelete
  83. Hello, I would like to subscribe for this web site
    to get latest updates, thus where can i do it please assist.

    ReplyDelete
    Replies

    1. Dear User,

      Thank you for your feedback.
      You can follow us on Facebook : Oracle DBA/Apps DBA

      Regards,
      ora-data Team

      Delete
  84. Thanks in support of sharing such a pleasant thinking, piece of writing is fastidious,
    thats why i have read it completely

    ReplyDelete
    Replies

    1. Dear User,

      Thank you very much for your feedback.

      Regards,
      ora-data Team

      Delete
  85. It is appropriate time to make a few plans for the long
    run and it's time to be happy. I have learn this submit and if I may I want to recommend you few fascinating things or
    tips. Maybe you could write next articles relating
    to this article. I wish to read even more issues about it!

    ReplyDelete
    Replies

    1. Dear User,

      Thank you for your feedback.
      Sure, we will do it.

      Regards,
      Sunil

      Delete
  86. Wonderful post. Thanks for taking time to share this with us. Keep us updated. oracle training in chennai

    ReplyDelete
  87. Excellent blog thanks for sharing the valuable information..it becomes easy to read and easily understand the information.
    Useful article which was very helpful. also interesting and contains good information.
    to know about python training course , use the below link.

    Python Training in chennai

    Python Course in chennai

    ReplyDelete
  88. Sharing the same interest, Infycle feels so happy to share our detailed information about all these courses with you all! Do check them out
    Best Data training in chennai & get to know everything you want to about software trainings.

    ReplyDelete
  89. Want to set your career towards Big Data? Then Infycle is with you to make this into your life. Infycle Technologies gives the combined and best Big Data Hadoop training in Chennai, along with the 100% hands-on training guided by professional teachers in the field. In addition to this, the mock interviews for the placement will be guided to the candidates, so that, they can face the interviews with full confidence. Once after the mock interview, the candidates will be placed in the top MNC's with a great salary package. To get it all, call 7502633633 and make this happen for your happy life.Big Data Hadoop Training in Chennai | Infycle Technologies

    ReplyDelete
  90. Infycle Technologies, one of the best software training institutes in Chennai offers excellent Oracle PLSQL training in Chennai for freshers and students, and Tech Professionals of any field. Other demanding courses such as Java, Hadoop, Selenium, Big Data, Android, and iOS Development will also be trained with complete hands-on training. After the completion of training, the students will be sent for placement interviews in the core MNC's. Dial 7504633633 to get more info and a free demo.Excellent Oracle PLSQL Training Chennai | Infycle Technologies

    ReplyDelete
  91. Great Article IoT Projects for Students

    Deep Learning Projects for Final Year

    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

    ReplyDelete
  92. You share information related to oracle and database queries. This post is very helpful for us. Moreover, If you want to get mover services for products delivery, Then you should hire professional moving company in Roswell for quality work.

    ReplyDelete

Thank you for your comments and suggestions. It's good to talk you.