Oracle DB Version - 10.1/Sun Solaris 5.8
Expdp/Exp hangs for more than 4 hrs, No errors were reported in the alert log and no blocking Session.
After some time received timeout session error.
By checking the above, we found as a root cause that Exp job started a week back didn't complete properly. It's holding up any new Exp job.
I Killed a Exp session from the database, but Still in the database as a "KILLED" state. So I decided lets kill the OS process of the Exp Session. But It looks like "ora_dw001_SID". I'm not sure that I can kill this process, So raised a TAR with oracle to get confirmation to do it.
After killing this OS process, Session went away from the database, I can start a New Exp backup without any Issue.
1. After killing the Session from the database, make sure It's go away from the DB and Server.
2. If you see any process like "ora_dw001_SID" that holds your Exp job, you can safely kill this, It's not a BACKGROUND PROCESS, It's a worker for Exp job.