Most of the colossi in Shadow of the Colossus are good, the 15th colossus can fuck right off though.
The Orochi battles in Okami, while slightly tedious, get a mention for death by inebriation and the crowning-moment-of-awesome finishing move in the first one.
Chronos in God of War III, while less of a boss battle and more of a walking, talking level, is brilliant.
While the archdemon from Dragon Age: Origins is really boring and frustrating, as you get close to the doors keading to the fight all you can do is take one last look at your team, who've stuck with you through thick and thin and think "well, this is it" as you save for the final time and open the doors, knowing the inevitability of whatever fate awaits you on the other side. Never have i experienced anything like that with another boss battle.