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

Why I Can't Move/Import VM in XenServer.

Ok this is what i found out, the metadata export/import via ' Backup, Restore and Update ' menu in xsconsole OR using ' xe vm-export filename=xxx metadata=true vm= ' will export all vm's metadata eventhough you've specify the vm in the latter, the former is ok because it is backup & restore tool. Problem happens when you want to restore the metadata or import to another xenserver. If you follow the method from 'Backup and Restore' in the reference guide, it should be ok if you're using pool because all pools are using shared storage; and if you don't use pool, your xenserver store all the VMs in the shared storage ONLY, means the Local SR is not use at all. This way, you wont face any problem. But, what if you have a mix of local & shared storage use. 
Post a Comment
more »

SQL using partition and connect by clause.

select id, max(ltrim(sys_connect_by_path(gred,' , '),' , ')) as mygred from (select b.KJ19SIJIL||b.KJ19KOD as id, b.KJ19GRED as gred, row_number() over(partition by b.KJ19SIJIL||b.KJ19KOD order by b.KJ19GRED) as myrow from kj19setara b) start with myrow = 1 connect by prior myrow = myrow -1 and prior id = id group by id order by 1;
1 comment
more »

Use raw disk for Apache Trafficserver in Centos 7

I want to use raw disk for my Trafficserver 5.1.0. I've created a partition at /dev/sda5 and in order to allow trafficserver to use the disk, please follow these steps. 1) Edit/create a file in /etc/udev/rules.d . I named it 50-ats.rules eg. vi /etc/udev/rules.d/50-ats.rules 2) Add these text. SUBSYSTEM=="block", KERNEL=="sda5", GROUP:="trafficserver", OWNER:="nobody" note : - sda5 is the disk/partition I want to use with trafficserver. - OWNER:="nobody" is the default owner when you compile trafficserver.
Post a Comment
more »