How do I generate random sample data in my Oracle database?

Does anyone know of a tool that can inspect a specified schema and generate random data 开发者_Python百科based on the tables and columns of that schema?

Another alternative is Swingbench Data Generator

It is useful to use the SAMPLE clause (for example generating order lines for a random combination of orders and products)

This is an interesting question. It is easy enough to generate random values - a simple loop round the data dictionary with calls to DBMS_RANDOM would do the trick.

Except for two things.

One is, as @FrustratedWithForms points out, there is the complication of foreign key constraints. Let's tip lookup values (reference data) into the mix too.

The second is, random isn't very realistic. The main driver for using random data is a need for large volumes of data, probably for performance testing. But real datasets aren't random, they contain skews and clumps, variable string lengths, and of course patterns (especially where dates are concerned).

So, rather than trying to generate random data I suggest you try to get a real dataset. Ideally your user/customer will be able to provide one, preferably anonymized. Otherwise try taking something which is already in the public domain, and massage it to fit your specific requirements. The Info Chimps are the top bananas when it comes to these matters. Check them out.

Allround Automation's PL/SQL Developer has a data generator tool. But be warned: it's a bit flaky - it seems to work fine on a single-table basis but gets tripped up when there are dependencies between tables.

I admit that eventually I just started writing my own SQL scripts to generate data. Turned out to be much more stable.

Have a look at Databene Benerator.

It's a bit complicated to do the initial setup but is quite powerful.

Bit of a wild card this one but thought I would mention it.

If you have data in a production environment that you can't use because it may contain sensitive information, Oracle have a product called "Oracle Data Masking" that will replace the sensitive information with realistic values.

I don't know the cost of this product but if you want more information, it can be found here.