The best Superbowls that I can remember off the top of my head are:
Bengals vs 49ers
Bills vs Giants
Packers vs Broncos
Titans vs Rams
Patriots vs Rams
Bucs vs Raiders
Patriots vs Panthers
Patriots vs Giants
My fondest memories are of course the Pats losing to the Giants, and the Bucs beating the Raiders. I know that was a blowout, but my dad grew up in St. Pete, and the Bucs were my dad's team from day one of their existance. He was a Phins fan before that, but became a Bucs fan when his hometown got a team. He watched every dismal game of every dismal season, and was very happy when they started winning. I'm glad that he got to see his team win a Superbowl before he died. That and seeing the Giants knock off the Pats are my fondest Superbowl memories.
Sadly, that is the closest thing that I can compare a Superbowl win by my team to. But, if the intensity I felt with those 2 games is any indication of what it will be like to see MY team win one, I may need an oxygen tank, and 911 on speed dial. I might lose conciousness.