Front Wheel Drive:
4T65e / 4T65e-HD (4 speed automatic)
Rear Wheel Drive:
4L60e (4 speed automatic)
Tremec T5 (5 speed manual)
The 3800 was only available with one single manual transmission option from the factory. This would be the Tremec T5 found in Camaro's and Firebirds.
The 3800 utilizes the GM 60 degree bellhousing. This is also known as the “metric” bellhousing. This is used commonly among most front wheel drive engines from GM starting in the late 80's. Most if not all front wheel drive v6's use this bellhousing. As long as the transmission has this bellhousing it will bolt up to a 3800, but you may need to make sure you have the correct torque converter and pilot hole size to make it work.
Due to having a 60 degree bellhousing as well as only one manual option, RWD transmission options are a bit limited unless you're willing to put in some more effort or money to make something better work.
Most RWD transmissions from GM use the old Chevy V8 bell housing, but an adapter plate can be used to make nearly any of these work with the 3800.
More information about other bellhousings can be found at this wikipedia page:
https://en.wikipedia.org/wiki/List_of_GM_bellhousing_patterns
Keep in mind if you're doing a swap, especially with a manual trans, that there are quite a few different flywheel/flexplate options that you could have on your 3800.
More information can be found about that here:
Flexplate and Flywheel Options
4T80e (4 speed automatic)
Getrag 282/284 (5 speed manual)
Getrag F23 (5 speed manual)
Getrag F40 (6 speed manual)
Muncie 4 speed (Fiero) (4 speed manual)
These are transmissions that are not as well documented or have not been attempted yet but look like they could work