I have never had the "oh god" moment in the sense of "when will this dive end." I did have a moment where I thought I was in serious trouble, and was beginning to hyperventilate. This was on my final Adv Nitrox/Deco Proc dive. I hit 147 and began to panic. I have never experienced a panic attack, but was later told that is what it was. Once I ascended 10 feet, I was fine. Dark narc??
A better story though is when I took my girlfriend and 11 year old daughter diving in Mexico. It was our 2nd dive of the day, and before we jumped in we could see the storm clouds beginning to build.
Being with my Jr OW daughter, we were limited to 40 feet, so had a good long dive. She was great. There was a pretty good current going, so she learned a bit about drift diving. After 55 minutes it was time to surface, and we had traveled a good way from our drop. We had a nice safety stop, watching the reef below us fly by. Our DM shot his bag and we just hung out. Upon surfacing one thing was missing. Where was the boat? There were 9 of us together, so they were not out picking up others. A decent swell had built, at the crest of which we could see our boat way off in the distance. Whistles were blown, arms were waving. We all watched as the boat left the area, in the opposite direction of us. Then we saw the lightning strikes out at sea. Oh well, time to swim. We were only about 1.5 km offshore, but well south of where we had dropped. It seemed like a cool challenge to me, except I had an 11 year old with me.
Some in our group began to get a bit panicky as the swim went on. It was a tough one. It was pouring rain and the lightning was getting much closer. My daughter was exhausted, and she was my absolute priority. I towed her for a bit, then she swam, then I towed, etc. My girlfriend was definitely having that "oh god I may die" moment. She was freaking out, but still swimming. As I got my daughter to a point where she could walk in to shore, I swam back out to help others. My gf was one of the last to get in, having lost her mask in the process. This was the first time I actually had to use some of the skills I learned in Rescue, dealing with panicked divers. The training came in handy.
The beach we ended up on was a few km south of the harbor, and on a locals beach. They were having a picnic as they watched exhausted divers drag themselves out of the water. As the rain calmed and lightning moved to the north, the shop owner pulled up with another boat to take us back to the dock. One of the DMs had swam in quickly to call for another boat.
I felt amazing. What an awesome challenge. Most everyone did something to help others out, from encouraging words to full out rescue towing. At the end of the day though, you are responsible for yourself (unless you are 11). Tough it out and swim, save your life.
I know the captain of the original boat was in trouble. We did not see him when we returned. The shop owner was completely apologetic. He even gave my gf a mask to replace the one she lost. Some of our dive group were pretty shaken, definitely having had that "oh god" moment (actually a lot longer than moment). I was pumped, who's up for round 2 (not really).