Skip to main content

Search oracle 'ghost' or hang connection with sql.

Here's a quick sql on how to search for session details including their running sql. The sql statement is as below :

select a.terminal, a.program, a.username, to_char((a.last_call_et/3660),990.99) LastCall, a.logon_time, a.sql_hash_value,
decode(a.sql_hash_value,'0',null,(select b.sql_text from v$sql b where b.hash_value = a.sql_hash_value and rownum = 1)) SQL_statement
from v$session a
where a.terminal like '%'
order by 4 desc;

a.terminal - machine name
a.program - what program taht make connection to our database.
LastCall - last time this connection make an sql call. The column is in seconds so we devide with 3660 to convert it to hours.
a.logon_time - logon time for this particular connection.
a.sql_hash_value - the key to link between v$sql & v$session. Just for checking purpose.
SQL_statement - sql statement called by this connection.

So, basically this sql will retrieve all connections sorted by 'LastCall' since i want to get the oldest connection. If you want to drill down to specific machine just replace '%' with a machine name from column 'a.terminal'. Easy huh! I suggest you use TOAD or SQL Developer to enhance your plsql experience.
Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Tips : EXTRACT function as alternative to TO_CHAR

You want to create a sql to list data group in year (datetime datatype). Example as below : The table is smp.SC03F_TEMP with over 10 million rows. Usually, if there something deals with date we will use to_char function. As an alternative, you can use extract function to extract certain part of the date. In belows example, we extract the year of a column. As we can see the time taken is almost half for 10 million rows. Happy trying. select count(*) from smp.SC03F_TEMP; -- 10,866,921 records select count(*), to_char(A.SC03TARIKH, 'YYYY') from smp.SC03F_TEMP a group by to_char(A.SC03TARIKH, 'YYYY'); -- Time taken 54sec
Post a Comment
more »

Kill the Ads, Save the Bandwidth.

Remember the time when your browsing is like a breeze, pages appear almost instantly and reading mail is... reading mail without distraction of little animation called ads at the side of your browser. Well those days are over as advertisement plays an important role in getting revenue and for some, thats how they make the site running. Sometimes ads are good as 'second opinion' when you are shopping online, but too much of it will clouds your decision and might end up buying unnecessary thing. For the organization, unnecessary ads will consume the bandwidth thus affecting productivity. So, how to control this ? If Firefox is your number one browser then fear not, ads filtering is very easy to setup. Just follow these simple steps :
Post a Comment
more »