The Oblivion CS is just like the one that came on CD for Morrowind, with a few enhancements. The game content was all delivered on the game CD or download, and the CS just gives you the tools to manipulate them. You'll need a few external tools, too, if you want to create some types of new content, but you probably already have things like Blender, and the GIMP, if you worked with Morrowind. The file formats, .nif, .kf, .dds etc. are the same, but Oblivion uses later versions than Morowind did, so the same file may need a pass through the tool to convert it up before you can use it in the newer game.
Oblivion starts off with single-mesh bodies, so something like Better Bodies isn't needed, but of course there are several mesh replacements available. OBSE takes extension of the script language a lot further than was done for Morrowind, and the vanilla scripting language had a lot more in the first place. It adds a lot of AI - especially scheduled wandering of NPC's, so there's a lot of new stuff associated with that.
Consider downloading CSE (Construction Set Extender) which enhances the CS in several ways, the most useful of which (in my opinion) is to use assets directly from the .bsa files, without having to extract them first.
There's a huge amount of modders' resources - new meshes, textures, etc. - available alongside the mods at the Nexus
, so you're not restricted to game content at all.