The inherent problem with this idea is rather simple. RPG Maker is not 2D - it's more of a 2.5D because it does in fact use shading to try and represent the illusion of height, width and depth. It's 2D that pretends to be 3D. You are seeing it from a looking down+forward perspective versus a straight up down perspective.
True 2D would be something more like the old Grand Theft Auto Games (even though they are also technically 2.5, they more closely represent 2D games) and in a game like this map rotation would be fine, especially if the player had control - but in terms of games made in RPG Maker? Not even worth the effort, you're really just better off making new maps for things like the ocean cliff, as Andar said. I actually do this in my mapping, quite often, though the player may not even be aware they've been reoriented - I feel in the case of MY game, were I to add such a compass it may actually confuse the player's movements, but my game is also pretty linear. And having the map change from S to N upon transfer could be confusing, making the player go back through the door they just came through. I do my rotations on transfer, so just like Ander said, the player isn't even aware it happens.
But yes, the problem here is if you actually want them to be aware, and want to rotate the map then all of the graphics, walls, etc. will have to be re-mapped for every direction, due to the fact that RPG maker is 2.5D and not 2D. If you have no problem mapping every single map 4 times from each angle, then you could do this, but it seems like it would take alot of effort for very little payoff.
EDIT: After looking at your comment I think you misunderstood what you saw, if they entered from the top and came from the bottom - it's not that the camera rotated, it was part of the transfer event - there is an option to make it so that on tranfer you face a certain direction, so even if you transfered from facing down, you could be facing up upon entering the next map. Nothing is actually rotated but the player, in that instance.