Are you referring to the system alert tones for SMS and email? If so they are in /system/library/audio/uisounds.
The one for email is called new-mail.caf. There are 6 for SMS, named sms-received1.caf through sms-received6.caf.
I replaced my sms-received4.caf and sms-received5.caf with "red alert" sounds from StarTrek and TNG. My phone is my pager and it is important that I don't miss a page when oncall.
I am currently playing with ringtones and system sounds by cutting short samples from SeaLab episodes.
You'll need to create audio files in .aif (aiff) format, and then rename them to .caf in order for this to work. Then copy them into the above directory using WinSCP or equivalent. I renamed all the original sounds to "old-whatever" so that they are not lost, I can put them back at any time. You also have to reboot the phone before the changed new-mail.caf will be assimilated.
I am using WinSCP. It opens a split-pane window with PC folders in the left pane and iPhone folders in the right. You can drag/drop from a windows explorer window (or from the PC pane) right into the iPhone pane once you've navigated the iPhone pane to that directory.
I've been using "Adobe Audition 1.5" to edit MP3s to the length I want, then using "switch" to convert the MP3 to AIF, then renaming the AIF to CAF. I rename the file I am going to replace on the iPhone (to save it) and name the CAF file appropriately as the one I renamed. Finally, I drop the CAF file into the destination directory on the iPhone.
Remember while using WinSCP to set the "auto-lock" feature of the iPhone (in settings/general) to "never". If the iPhone goes to sleep while you are using WinSCP the connection gets busted and you have to wake up the phone and re-connect.
I probably should have added that you need to have WiFi to do this...