[ineedasername]

We just have a live clone in Dev, updated monthly, and a dev instance of the front end to use it. Sometimes monthly is too long, so a DBA will run a manual update in off-peak times. Queries that don't write data back can be moved directly to prod, though we also have an ODS with denormalized data for easier creation of reports & analysis. And changes that significantly write back to the DB are moved to test first, then to prod. Sometimes different people have things going on and that requires different timing or a clean copy of dev or test, and we'll temporarily spin up another instance.

To be fair, the above description paints a better picture than we have in reality. There's nuances and edge cases. But prod is kept pretty clean. Most of the problems we have are related to upgrades-- these are enterprise apps that all use Oracle, and the latest updates for one might require a particulate version of Oracle, but another app will be in conflict with that version. So a lot of the DBA work involves wrangling support from vendors on how to work around these. You'd think an app using Oracle 12c would run fine if you upgrade to 13c, but no it doesn't.