I would say the Most Tense, action packed game that had me on my toes was Ninja Gaiden I for the Nintendo. The eneimies attacking you as you had to make crucial platmorm jumps coupled with the fact that if you lost all of your lives, you had to start all the way at the beginning of the game. Thats true difficulty.
Also RE2, Parasite Eve and Silent hill 3 were very tense.
Also a very good tense game was Gitaroo Man. Trying to hit notes and such was very challenging and demanding.