Schema transfer between Production / Training / Testing / Development Dbs?
Scenario:
Presently we have our main department schema named PKS (80 tables) that resides on all 4 of our environment databases / all separate servers (Production / Training / Testing / Development - all version 9.2.0.5.0 Enterprise Edt on Win 2000 OS). Every single one of our in house developed applications relies on PKS for staff info, security, authentication, address info, etc....
Goal:
I am trying to figure out the easiest, lowest maintenance and most reliable method of transferring our PKS schema(tables) on the Production Db to all other existing PKS schemas(tables) on Training / Testing / Development dbs. On a nightly basis.
Please Note: I only want this refresh of PKS data to occur in one direction. From the Production db to the Training / Testing / Development dbs. And not vice versa!! I also want the PKS schemas on Training / Testing / Development to remain fully functional (updateable) (all constraints, etc remain intact) after the refresh occurs.
What is the best method of data refresh for this type of scenario?
Any suggestions or advice would be appreciated.