Oracle user creation hanging.
One day, I was doing my work and my application team called me regarding slow response of database. I kept my other less prioritized work in one side and started to analyse the things. I got a login to the database server and then called application team again to ask, what exactly they are facing. They told me, that "My account creation script is hanging. Which is working the since last 10 minutes. Account creation is not happening."
Normally, what DBA do? I started exactly the same thing. Checked locking session issue. Found no locking issue. Then started to check alert logfile. But no luck. No errors printed in alert logfile yet. Then I checked server Load average with uptime command, and found it to <1. So, I was wondering, that which is the cause. I goggled for 10 minutes but no luck. Account creation is hanging and no errors found in database server.
Then I started to think from ground level. When any query can not give the result? I answered my self, that when it can not have resource, and then I checked top command in my database server and surprisingly checked that my database server CPU utilization 100% because of one inactive session. I killed it immediately and CPU utilization decreased. Application team started to work fluently.
Some times our core knowledge can only help to come up with perfect solutions.
