I don't remember when and where I first came across Seers in fiction, but I remember some of my favourite Seers and soothsayers being from the Asterix comic series and the He-Man series.
I don't know what exactly fascinates me so much about Seers and Oracles as I don't even read my horoscope with any regularity, I just love their powers. To be able to see into the past or future, it sounds wonderful despite the fact that in fiction they are always depicted as leading very lonely lives and are isolated from the rest of the community. Maybe this is what I relate to as I'm a loner too.
The other thing that I love about their powers is their power of telepathy. Of course, it makes sense that if a Seer has chosen to live in isolation they should have some means to share their visions with the person(s) who could do something about them. But that makes them really awesome and all-powerful.
Then there is the flip side. By making them all-powerful, there is the danger of losing the reader or viewer's interest and sympathy for these characters, which is why I love the solution that writers came up with: Seers may see and hear and know everything, but there are limits to their power; they are given only so much influence to do some things about the things they've seen or heard but after that, the Seer must turn to other characters (protagonists) to do the right thing.
Example, the Seers from Tsubasa: Reservoir Chronicle did not have limitless powers. Yuko had to wait for someone to make a wish before she could find ways to help the people she had visions about. Ashura-ou lost his mind because he tried to change Fay's fate, to change the things he had foreseen. Princess Tomoyo was not allowed to speak of her visions, especially to the people the vision was about.
Putting limits on their powers makes their story line very tragic. It's also the reason why I love Galadriel (from The Lord of the Rings movies series) because the only thing she could do was give hope and courage to the Fellowship but things had to play out the way they were meant to.
There's also the aesthetic appeal. Most Seers and Oracles are portrayed as being incredibly beautiful. They are soft-spoken and gentle, even kind. Their isolation also makes them appear vulnerable and fragile which brings out our sympathy and protective instincts.
But there are also comic Seers like Professor Trelawney from the Harry Potter (book) series. Though she is related to a famous Seer, she is just the comic relief and thus has more in common with Orko (from He-Man). She is batty, dresses suitably to highlight the many eccentricities of her lifestyle and overcompensates for her lack of psychic ability by making theatrically bad predictions. The only time she makes real predictions or prophecies, she doesn't even remember making them afterwards! She really cracks me up.
So these are some of my favourite Seers. I hope you liked my post, thank you for reading.