Very good responses so far everyone.
I would like to develop the discussion further by going into more detail. To me, it is incredibly fascinating that Star Trek in general has aroused more feelings of eerieness, spookiness and creepiness - in me, that is - than almost anything else on television or film which I have seen.
If this is the wrong place for more in-depth, psychology based discussion, then that's fine, but if it's ok, I would be very interested to hear some really in-depth reasons from you guys about why you think various scenes might have particularly frightened/disturbed you, or made you feel uneasy.
Why, for example, does an episode such as Frame of Mind carry a more genuinely creepy atmosphere than a million and one psychological thrillers and horrors out there which ought to be scary, but often simply aren't? Is it because of the focus on psychology? The scene in which Riker partially "re-lives" his apparent murder of a man who followed him down a dark alley at night is, due to nothing more than music and acting, a really eerie, atmospheric and seriously creepy scene - and yet nothing happens! Frakes sells the scene because his acting is so good here - but it's just that. Him, facsimiles of his crewmates acting out how he felt emotionally, and what he's saying. That's all. It's one of the most underrated disturbing scenes in TNG, IMO.
It seems that any time Star Trek wants to be scary or creepy, it is able to do so with impunity. I can't think of an episode that tried to be scary but failed miserably...the production team, scriptwriters and actors pretty much always managed to pull it off really well.
I'll never forget Voyager's "One", which managed to be terrifying through nothing more than psychological manipulation and atmosphere, much like Night Terrors. Remember the imaginary alien: "Hasn't it got to you yet?" "What?" "The loneliness..." - and then he offers to spend more time with her.
Again, that's something that really shouldn't have been very creepy...but it was. Could it be the sci-fi setting? Maybe science-fiction does deal with themes such as alienation, being alone (or not so alone) in the depths of space amongst the unfamiliar, etc, better than most other genres...how else could such innocuous conversations seem so menacing and disturbing?
And again in TNG, there's an episode which deals with body horror...Identity Crisis. Was it meant to be scary? Considering it's not usually remembered as such, perhaps not. But I would say it is.
One of my favorite creepy moments isn't even a scary episode. The Xeroxed Picard in "Allegiance" singing in Ten-Forward is one of the best WTF act fadeouts. It's just too bad that we as the audience already knew more than the crew did.
It might be interesting to see a fan-cut where it's only the crew's viewpoint scenes.
Very cool mention, Melakon. That's another episode which has its fair share of scary moments and an eerie atmosphere: just imagine being trapped in a room with three other people, two of which you found it virtually impossible to get along with and one of which was potentially a savage killer? What about the score? Ron Jones used some kind of weird arpeggiated progression made out of Augmented Major Seventh chords (I think) near the beginning of the episode to create tension and a feeling of alienation - what a master composer! His presence really helped to create a believable soundscape for TNG's world in the first four seasons. Shame he was fired...
