The biggest one that comes to mind right now was
in FF6... waiting for shadow on the floating continent.
During my first time through I was feelin' all the drama
of the moment, with the tense music and the screen shaking,
I came across the prompt "WAIT FOR SHADOW?"" I waited
and waited, not knowing if he'd actually show up or not...
The countdown timer crept closer and closer to 0, and
I was about to piss in my pants from all the pressure. I decided
"eh screw it, I'm NOT risking starting all over and going through
all those bland cutscenes again for this... I'll see shadow again,
I'm sure of it"... Thinking I'd get shadow back, I save my game
in the World of Ruin. When I found out that I COULDN'T get
shadow back, that was my big OMG moment... I sat there
shocked... losing one of my favourite players for the rest of
the game.