Subsetting Production Data

Just about every database out there has at least three copies of itself: production, test, and development. Some shops take this even further with separate test and stress-test instances. It's pretty obvious that production is the real deal and hence the source of data for the copies. But how do you make relationally correct, subset copies of production data for both test and development? Oracle provides no tools for simply taking 10% of the production data and copying it to another database. The DBA or developer must either write extensive scripts themselves or utilize TOAD's new Data Subset Wizard (debuted in TOAD 7.3).

TOAD's Data Subset Wizard is a simple four-step utility for automatically generating relationally correct and efficient SQL scripts to copy a user -defined percentage of data from target to source database. This utility works for both the scenario where the target tables already exist and all you need is the data, as well as the scenario where the target objects have not yet been created so that you need both the database objects and their data. The instructions for using the Data Subset Wizard are different for each of these scenarios, so they are covered separately.

The first step in the Data Subset Wizard, shown in Figure 9.56, is simply to define your source and target databases, plus the name of the generated SQL file and whether to open it in Notepad or TOAD's SQL Editor. You can launch this screen from the main menu at Tools, Data Subset Wizard. Regardless of which scenario you are addressing, this first step is the same. Also note that there are Load and Save buttons on this screen. Thus you can save your selections to a named file such that you can run the same choices at a later time. Thus you could create one master set of choices for a subset of production to test, and then reuse those same choices by merely loading that file and just changing the target from test to development.

Figure 9.56. TOAD Data Subset Wizard ” Step 1.

The second step in the Data Subset Wizard, shown in Figure 9.57, is to decide whether you're working with just a subset of data copied to pre-existing target objects or whether the target objects need to be created as well. In this first example, the choice is Do Not Create Any Objects, Just Truncate Tables and Copy Data. This will modify both steps 3 and 4 in the wizard. Note also that the rest of the screen in step 2 is disabled for this option.

Figure 9.57. TOAD Data Subset Wizard ”Step 2/Copy.

In the third step, shown in Figure 9.58, you specify the percentage of data to copy, whether it's OK to use nologging and parallel DML, and some basic script generation options. Note well the Min # Rows in Lookup Tables value with a default of 10. This specifies the minimum number of rows to copy from source tables if their row count is low. For example, on a table with just 10 rows, you would get all 10 rows (the minimum) rather than just one row (10% of 10).

Figure 9.58. TOAD Data Subset Wizard ”Step 3/Copy.

In the fourth step, shown in Figure 9.59, both the Extents and Tablespaces tabs are disabled because you are just truncating pre-existing objects and loading them. So you merely click the Build Script button and get the script shown in Figure 9.60.

Figure 9.59. TOAD Data Subset Wizard ”Step 4/Copy.

Figure 9.60. TOAD Data Subset Wizard ” SQL Script/Copy.

When you need to create the database objects as well as populate them with a subset of the source data, you would instead on step 2 check the box for Create These Objects and Copy the Data, as shown in Figure 9.61. Note how this choice enables the rest of this screen's check boxes. You merely check all those objects that you want created and copied into the target database. You can use the context menu for options to Select All and Unselect All.

Figure 9.61. TOAD Data Subset Wizard ”Step 2/Create.

The third step remains exactly the same and is shown in Figure 9.62. Note that the parallel is for the INSERT SELECT hints and not the CREATE commands. It's step 4 that radically changes when you're both creating and populating rather than just copying the data. So review the following very carefully .

Figure 9.62. TOAD Data Subset Wizard ”Step 3/Create.

The Extents tab, shown in Figure 9.63, is a bit complicated as it permits you to define three distinct kinds of complex information. First, the top left portion of the screen offers check boxes for forcing and scaling certain storage parameter values. These options are fairly self-explanatory. The group box on the top right side of the screen permits you to define what is considered small, medium, large, and huge based upon either object or extent size. These settings are referenced on both the bottom portion of this screen and on the Tablespaces tab. The group box on the bottom of the screen permits you to force the object extent sizes based upon the object's relative size. For example, medium- sized objects (that is, those less than or equal to 5 megabytes in size) should use 512KB extents. This is a complicated tab. But if you spend the time to set all these options correctly, the data subset scripts you can generate will be awesome .

Figure 9.63. TOAD Data Subset Wizard ”Step 4/Extents.

The Tablespaces tab, shown in Figure 9.64, permits you to relocate objects and object partitions from one tablespace to another. You can move them all or by their size. In this example, the settings indicate to relocate small objects to tablespace USER_DICT, medium objects to tablespace USER_DICT, large objects to tablespace QADB_LOCAL, and huge objects to tablespace DWDB_LOCAL. Remember, you already defined the meaning of small, medium, large, and huge back on the Extents tab.

Figure 9.64. TOAD Data Subset Wizard ”Step 4/Tablespaces.

Now you click the Build Script button and get the script shown in Figure 9.65. Note how the CREATE commands first create the database objects, and then the script is the same as before and copies a subset of the data.