When I smear oil onto a scrap of paper and rub it in, the paper becomes quite transparent; but when I attempt the same with water it doesn't as much. Why?
The thing is that paper fibers are really transparent (unless the paper has been painted some color, of course). The only reason paper blocks light is that its fibers are all “immersed” in air.
Try to imagine what you would see with a very potent microscope: various clear tubes going in all directions. What happens to a ray of light entering this maze of “transparent tubes”? It gets refracted each time it enters and each time it leaves each tube. The final result is that light might scatter in any direction, but it´s more probable to leave by the same surface it entered, that means: back to you. That makes paper white.
Now, if you fill all this space with a fluid (by soaking paper in said fluid) and this fluid has a refractive index similar to the fibers, the light will not “feel” so much when it passes from fluid to fiber. The end result is that light will not scatter that much, and the paper becomes more transparent. The better the matching between the refractive indexes the more transparent the paper will be.
What you are noticing is that oil has a refractive index that is more similar to that of the fibers than water has. Still, water is closer to target than air, so paper gets a bit transparent with water too.
ps: this is also related to your other question ("wet is dark"). If the object is thick (imagine a pile of paper, or sand). Wetting it will enable light to penetrate more. Less of it will be reflected back to you, making the object look darker.