Well, a lot of the games I would think of as being really great were also very popular, and have already been mentioned, so I'll go with something a little less known: Earthbound, for the SNES. It was a great RPG, and absolutely hilarious. I've never had more fun playing a game than I did playing Earthbound. It wasn't anything incredibly special in any way, but it was just plain
fun.
Some other obscure or older games I thought were fun:
Blast Corps (N64) - Who doesn't enjoy destroying buildings with bulldozers and other equipment?
SimFarm (PC) - An entirely underrated member of the Sim series
Animal Crossing (GCN) - A great idle time-waster. I can go around for hours just pulling weeds if I'm bored
Legend of Zelda: A Link To The Past (SNES) - Not obscure and already mentioned, but the best Zelda game ever, IMHO.