Skip to content

Find Active Sessions in Oracle Database

June 8, 2008

There are times that you may need to issue a “SHUTDOWN IMMEDIATE” command to an Oracle Database. It is critical that you should consider checking who are the users that are currently running sessions on that Database. You don’t want to shutdown on your Boss or VP on Finance. Here’s a simple SQL to find all Active sessions in your Oracle Database:

  1. SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
  2. FROM V$Session
  3. Status=‘ACTIVE’ AND
  4. UserName IS NOT NULL;

-Marlon Ribunal
kick it on DotNetKicks.com

Advertisements
7 Comments leave one →
  1. NetWisdomSponge permalink
    November 28, 2011 3:24 PM

    Department to Prevent Redundancy Department…

    Isn’t it a bit silly to include STATUS in your query results when you’ve specified ‘ACTIVE’ as a criteria ??

    Sheesh!

    – Jim

  2. David Paterson permalink
    November 18, 2010 2:49 AM

    Interestingly this does not show oledb/odbc connections!

  3. Sopan permalink
    August 10, 2009 1:55 AM

    This functionality is readily available as a menu item in PL/SQL Developer (Tools->Sessions…).

  4. Arshad permalink
    May 18, 2009 2:46 PM

    Cool….Thanks

  5. Alexwebmaster permalink
    March 3, 2009 12:36 AM

    Hello webmaster
    I would like to share with you a link to your site
    write me here preonrelt@mail.ru

  6. Johanny permalink
    June 26, 2008 5:01 AM

    If you have a RAC configuration, you can use the next SQL to show the same information plus the instance identification on which the session is connected to:

    SELECT Inst_Id, SID, Serial#, UserName, Status, SchemaName, Logon_Time
    FROM GV_$Session
    WHERE Status=’ACTIVE’ AND
    UserName IS NOT NULL;

Trackbacks

  1. 2010 in review « Marlon Ribunal's Note On SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives


  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video

  • Software Blogs - BlogCatalog Blog Directory
  • %d bloggers like this: