Blue Stinger for Dreamcast and Rascal for PS1. Every time you enter room in either game, the camera is squarely pointed at the main character's head, looking down. Those games' camera also had a bad time with tight corridors, which both games, being a zombie game and a budget title respectively, had plenty of.
Robot Alchemic Drive for PS2 deserves an honorable mention, as the camera automatically whips around to look at key buildings when they fall. Problem is, this doesn't pause the giant robot fistfight that might be happening a few meters away from our squishy human player character. The only saving grace is that the camera can be forced to ignore these events by manually adjusting the camera, not that it tells you that...