Scientists can be famous. Think Einstein, Newton and Pavlov. You have heard of all these guys. Having said that, it takes a pretty major discovery, invention, theory, or breakthrough to make the list of famous scientists. As an Oceanographer, you probably shouldn't expect fame.
There is always the chance that you discover the lost city of Atlantis or come across a previously unknown species of whale, but those chances are pretty slim. What you can expect is a sense of scientific respect from your fellow Oceanographers, who are all striving to understand a seriously complex topic.