Secret Chamber is an unusual counter; it doesn’t stop the attack, like Moat does, and Watchtower sometimes does. But it can render an attack useless (or even helpful), and nowhere is this more evident than Pirate Ship, possibly the most reviled attack in the game. Simply reveal the Secret Chamber, then replace the top two cards of your deck with non-Treasures (ideally, Victory cards). A Pirate foolish enough to continue with the attack will only help you, by discarding cards you don’t want.
Secret Chamber works even better against Thief (because the attacker does not have the option not to go through with the attack, and is thus forced to discard your Victory cards), but Thieves are weak enough that you can usually just shrug them off. By contrast, Pirate Ship is one of the strongest attacks in the game. Secret Chamber stands out as one of the few effective defenses against Pirate Ships that doesn’t involve clearing your deck of all your money (which leaves you desperately poor) or surrounding yourself with Moats (which leaves you clogged with terminal +Cards Actions).