Mine would have to be the first colossus in Shadow of the Colossus. The awe I had when I first saw it walking around, and all I could think was "how the hell am I suppose to take down THAT?!"
For me it's probably any boss in Mischief Makers. Especially the dragon one or the one where you ride a cat, catch rockets and throw them back at the badguy. How exciting isn't that?
I think the boss that gave me the biggest rush was in a not very well known rpg called Silver. I was playing with my cousin and we were really involved with the game. We were so pumped and screaming every time the boss came close. It was a long fight and I couldn't do anything against the boss, until my cousin innocently said "hey, use the shield", and I was like "pff, the shield is useless". Then when I finally used the shield it was a super effective weapon against the boss and I killed him. I've always listened to his advices ever since