Reading about new features is great, but finding data to test them out on is sometimes troublesome. As DBAs, we’ve created test databases countless times, but the majority of the time they are empty.
Oracle does provide sample schemas that can be installed automatically by using the database configuration assistant (DBCA) when creating a database or manually by executing scripts under the Oracle software home directory. Consult the documentation of your particular version for more details. While these sample schemas do provide a framework that mimics a common layout, they lack the volume of data that typical production systems have. If your employer allows “sandboxes” to be created to test new versions and features then you can stop reading here because you are one of the fortunate ones. However, if you do not have this opportunity but would still like to create a sizeable data set to work with, there is a utility called Swingbench that fills this need very well.
Swingbench was created by Dominic Giles (http://dominicgiles.com/index.html) as an Oracle database load generator, but has since become a very useful suite of tools including Data Generator, Trace Analyzer, and several monitoring tools. Best of all, these tools are free to use. I would recommend that you provide a small donation if you find them useful, though.
Quoting the creator, “Data Generator is a utility to populate, create, and load tables with semi random data. Users can specify sequences, random text, random numbers, database columns, and files as sources for tables.” This sounds pretty good.
I recently downloaded Swingbench version 2.5 which includes the Data Generator utility and worked with it on a Oracle VirtualBox virtual machine running Oracle Enterprise Linux (OEL) version 6 update 6 with and Oracle version 220.127.116.11 non-container database. Here are the steps I followed to create my test data set:
- Swingbench requires Java 8 JDK. Download the jdk-8u111-linux-x64.rpm and install it (as the root user) with the command: “rpm -Uvh jdk-8u111-linux-x64.rpm”. Note that this file is for the 64-bit version of OEL.
- Download the swingbench25971.zip file for version 2.5 from the website mentioned previously and place it in a location where the oracle user account has read, write, and execute privileges.
- Unzip the swingbench25971.zip file. it creates a “swingbench” directory in this location
- As the oracle user, set the following environment variables if they are not already set (actual values depend on your environment):
- Go to the swingbench/bin directory. You have the option to create the sales history schema (shwizard), the calling center schema (ccwizard), or the order entry schema (oewizard).
- Once you execute the desired wizard file, the interface appears
- After clicking Next, you have the option to create or drop your desired schema.
- For the connection string, use //<servername>/<database_sid>. Make sure you can ping the host by the <servername> used and that there is a listener present providing service for the <database_sid>. Leave the connection type as the default (jdbc thin). Leave DBA username as ‘sys as sysdba’ and make sure the password is correct. Press Next to connect.
- If the connection is successful, you will see the next screen where you can choose the username, password, schema tablespace, and tablespace datafile of your data set schema. I like to keep my data separated for easier management, so I elect to create a separate tablespace and datafile.
- The next screen allows you to choose database options such as partitioning (range or none), compression (yes or no), tablespace type (bigfile or normal), and indexing (all, primary key, or none). Be careful here. Do not select options that you are not licensed for in systems that are eligible for license audits.
- Sizing options come next. I don’t suppose I need to tell you not to size your schema any bigger than you have space available. What is good here is that two counters at the bottom will let you know the size of the schema tablespace and the size of the temp tablespace required based on your selection. Click next when you are done.
- Finally, select the level of parallelism and click finish to create your schema.
- The wizard log popup appears and the main wizard updates to tell you the progress of the creation process
- Once the build is over, you should see a “successful” message popup window. Click Ok to acknowledge the message and close the window. You then have the option to review the wizard log in the wizard log window or save it to a file. Close both the wizard log and main wizard windows after you are done.
At this point, you have the option to generate other data sets if you want to. Enjoy.