I was getting bored getting a Red Alert every time somebody spoke my name at IRC [the sound is bundled with GAIM messenger], so I went looking for free sound effects. First, I came along this site with loads of various sounds, grouped by category and these misc sounds. Quite entertaining [try downloading all Beeps, for example [wget -rl 1 <url from page>], and play them after each other in your favorite mediaplayer :)]. I got some SciFi related sites from #morphix on irc.freenode.org too, like this site with misc sounds from the original Star Trek series and Babylon 5 sounds [doorbell is nice as alarm]. I settled for the Babylon 5 doorbell, but I've quite some sounds in stock to do some cool stuff.

Ah, for good measure, here's an overview of some sound effects sites too.