Create a Test Database copy of Production with only a sample set of Data

Question

We have 2 Oracle databases. One is our Production customer database. The other is a test database. The Test database is a copy of production without any data...just empty tables. I am trying to come up with an efficient and effective way to copy a sample set of data from the Production db to the Test.

I need to start with a sample list of PKs from our Customer table and write a process to populate the test database Customer table with that sample. Then, I need to populate child tables with relevant data based on the sample.

We used to have a Java process that would use a prepared statement to select data from Production...build a file based on the result set...then use an update statement to get it into the Test db. It was horribly inefficient.

I am not a DBA so I don't know much of that realm. I tried to Google some info about Database Links but couldn't find very good examples.

Does anyone know of a way to do this...or, at least give me a starting point?

Answer 1

If you have enough space, I would suggest copying the entire production data to test. It will be a lot easier to administer, it could also be a good oportunity to test your backup (restore from backup to a new instance).

From a developer point of view, you won't be able to test the performance of your application reliably without a representative set of data. This data set should have the same properties as the production data (data volume, physical distribution...). The easiest way to achieve this is to have the same data in test as in production.

If you can afford downtime you could stop the production db, copy the file to the test server and mount both databases. If you can't afford downtime it might be a good idea to pickup some DBA skills (and eventually learn about hot backup then restore to a new instance).

---

Update: if physically copying your database is not feasible, you should look into bulk copying data with expdp and impdp (or the old exp/imp). You can either copy all schemas or filter the data on export. You would choose appropriate WHERE clause by hand in this case. Exporting and importing in bulk will be orders of magnitude faster than copying data row by row.