As an example of one that's awful, the Star Wars PT.
Just... you know, ancient prophecies are incredibly common in fiction, so I want to hear some that are actually well used and legitimately make the work better.
-- Not changing this sig until I decide to change this sig. Started: July 6, 2005
But seriously, I thought the prophecy in Harry Potter was played pretty well although it really isn't ancient. I've heard Wheel of Time is based on a huge amount of various prophecies and manages to make it work, although I've never read it.
-- SuperNiceDog buried everyone else's hopes of winning the Guru contest in the backyard.