I recently replayed it in late 08. I popped in the cartridge and played it the way it is meant to be played - on a 1996 TV with a N64 controller.
A few Pros/Cons.
Pros:
It still has the best soundtrack (although that is heavily challenged by SMG)
It has an amazing story, considering that it is very non story heavy.
It has a fantastic nostalgia feeling.
The "atmosphere" is marvellous.
Cons:
Its graphics and AI are heavily outdated.
Its controls are far from as good as in most Nintendo games from 6th gen and out.
The "gameplay" (I hate that word tbh) isn't as fleshed out as in many later games.
In plain text.
The atmosphere makes me love playing it. I have TONS of memories from the game (first time we played, my entire family (5 people) played and we used over 100 hours, as we were all completely rubbish at games).
On the other hand, it feels a bit... Simplistic. You face your enemies in the same type of combat every time. You lack a lot of options that are implented in later games.
By 2008 standards, it's a good game. It's not awesome or a masterpiece by these standards, but it's still very playable.