Friday, December 25, 2009

How to check for blocking session in oracle?

It happens many a times that a user calls you and tells you that he has executed a query a long time back which is a very small update or delete statement and the Sql screen is in a hung state. In this case it is proably the user session is being blocked by some other session. So the easiest way to track this is using the v$session view in oracle 10g. This view has a field blocking_session, which will tell you what sid is blocking your session.
e.g
suppose user joe calls and tells you the same problem, his userid is XY123
-
sql>select sid,serial#,blocking_session grom v$session where username='XY123';

output

sql> 523,1234,321

so here we can see session 321 is blocking session 523 of joe.

I have always found this useful as i come across this situation many a times.

Sunday, December 20, 2009

Unix - whoami and who am i

In many unix books most of us have read these words or commands whoami and who am i..These commands are useful when you work in an environment where people have different login ids and you are using sudo access or are first logging thorugh a different name and then switching to another user.
suppose i login with username joe and then i use su - to switch to user pam. Now if i will use the command
$id
joe gid() uid()
$su - pam
password: *******
$
$id
pam gid() uid()
ok...now i will use whoami
$whoami
pam

ok and now i use who am i

$who am i
joe

So you see the difference. its very clear from the example.
Sometimes you might see many screens open, but you really don't know who had initially logged into the system . At that time these commands are very handy.

Saturday, December 19, 2009

Track long running queries in Oracle

It happens sometime or shall i say most of the time that your manager or user calls and asks the DBA ...the db is very slow..can you check what is running in the system or can you check for any long running queries in the database. In this case oracle has provided us a view call v$session_longops, this is a fantastic view and it really helps in checking out for long running queries. for a simple example just run the query -

select * from v$session_longops where sofar <> totalwork;

if it is RAC you should use gv$session_longops.

This is just a basic example and will give you lots of information about long queries running in the system, and will also tell you how much work is done and how much work is left..time shall i say..

Saturday, December 5, 2009

Oracle Database- Who is using my undo Tablespace??

It Happened once that i came in morning and logged onto the database. I was just checking tablespaces when i observed undo tablespace is totally full....so what was eatin up my undo..in such case oracle provides you a view called V$transaction that gives you information about transactions using Oracle db undo tablespace. This view can be joined with V$session and with help of this you can easily come to know which user is using your undo tablespace. In my case some developer has left running an update query which was to update a major table at night and had not committed..I called up the developer..and told him to either commit or rollback..he committed his transaction..and undo got freed..