The navy likes to see these old ships set up to memorialize the service of millions of sailors over the last century. But currently, the navy is really pushing to find homes for some special ships, namely two carriers, two battleships, an experimental stealth ship, and the Glomar Explorer (a CIA funded, barge mounted, salvaging system for retrieving a sunk Soviet missile from deep water in the 1970s.) Except for the stealth ship, turning any of these ships into museums or memorials will cost big money, and there aren't too many folks, with deep pockets, eager to step forward. So the Glomar Explorer, and maybe even a battleship or two, may end up providing cheap housing for fish.
The U.S. Navy has long made decommissioned ships available, free, to government or non-profit organizations, for use as museums or monuments. But with all those World War II era ships that went that way in the 70s and 80s, the current crop of Cold War era ships are having a hard time finding retirement homes. Over a dozen ships are currently available from the U.S. Navy, but there are few takers. While the navy selects a few ships specificlally for memorial duty, most retired ships these days get sunk to provide reefs (for fish and tourists), or are shot at for weapons and damage control testing. Some of these ships can be saved if a locality or organization can make a case for getting to for a memorial. It's too expensive to break up these ships for scrap, because of the cost of meeting environmental rules.