Re: HELP!!! HORRIBLE ARCHITECTURE

 I'm going to assume that these schemas are virtual (separate) databases that have been put into one instance because someone thought that it was a good idea. I'm making that assumption because you talk of 10 defferent versions of the software being under development. If you have 10 versions of the same application under development simultaneously then your company has greater problems.

So, assuming maybe 5 applications with two development versions each. Each of those applications uses its own set of schemae as a virtual database. Give them only the schemae that they require for their development. And give them a logically consistent subset of the production data. (Really, they shouldn't have access to production data --- they should be using synthetic data that has all the characteristics of production data but none of the identifiers that could allow them to select a genuine transaction.)

I'm also assuming that your developers don't have DDL rights on the development database. If they do then revoke them. Only DBAs have DDL rights. Java programmers make terrible DBAs.

I'm also going to assume that each schema is segregated by tablespace. If not, they should be.

After you build each development database (with a subset of schemae and data) take a cold back-up before you turn it over to the developers. When they want changes to database structures, you approve, build the scripts and run them under configuration control.

When the developers want a refresh, you restore the original cold backup and apply any approved DDL. None of this "refresh with new data from production."

You'll also need a test and integration database where each of these applications can ensure that it plays nicely with the other applications.

I don't envy you.

--
http://www.freelists.org/webpage/oracle-l