This section guides you through a worked subsetting example in Oracle. It includes steps to create a small source database and to create an empty target database from it, which is then used in the subsetting example. It provides links to further examples and to resources to understand how the subsetter works.

Contents

Preparation

Preparation of Subsetter

Please do the following before beginning the worked example

1. Install the subsetter CLI.
2. Verify your installation by running the following command in a terminal window:
   1. Windows cmd: subsetter.exe --version
   2. Windows PowerShell: ./subsetter --version
   3. Linux: ./subsetter --version

The subsetter should report its version number as below (example in Windows cmd).

C:\Subsetting\>Subsetter.exe --version
0.8.0.0

Preparation of Database Engine

You will need the Oracle Instant Client. Specifically, you need to also install the SQL*Plus Package so that you can access the command-line SQL query tool, sqlplus. Oracle Instant Client and SQL*Plus are downloaded as .zip files, so you would need to find your own directory for placing them and making sure that they are extracted into the same folder. You can run sqlplus by opening a command prompt directly in this directory, or add the directory to your PATH environment variable to enable running sqlplus from any where on your computer.

Executing SQL queries in sqlplus requires setting up an Oracle administrator account and password. The administrator account is typically SYS. After creating the password and finishing the setup, execute the following at a command prompt:

sqlplus sys@localhost:1521/FREEPDB1 as sysdba

Enter your password when prompted. If successful you should see something similar to the following (can vary depending on your Oracle and SQL*PLUS version):

SQL*Plus: Release 23.0.0.0.0 - Production on Wed Nov 1 10:53:53 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>

Now you have entered sqlplus and you should be able to execute SQL queries against the databases you created. To exit sqlplus, simply type:

quit

Create a source database

Create and populate the database

Download the SQL script sample-oracle-database.sql. You will need to edit the line CONNECT "SourceDatabase"/P455w07d@FREEPDB1; This line assumes the Oracle service is FREEPDB1 and that your password is P455w07d. If you use a different service and/or (hopefully) password, you will need to make changes accordingly.

You need to execute this script file in your Oracle server using your administrator account. Here is the command to execute the script in sqlplus:

@/path-to-script/sample-oracle-database.sql

This will create a user "SourceDatabase" with four tables, containing example data. It represents a simple social media website, where users create posts and comment on posts, and users belong to organizations.

Check the database contents

Check that the tables exist and contain data. Use a SQL query such as this below which lists the data in a table:

SELECT * FROM "Users";

And a SQL query as below which to list the data in a table. The database name needs to be in full capital:

SELECT object_name FROM dba_objects WHERE upper(owner)='SOURCEDATABASE' AND object_type='TABLE';

Create an empty target database

Download the Windows batch file MigrateSchema_Oracle.bat and the two SQL script files GetSchema_Oracle.sql and CreateUser_Oracle.sql. Put all three files together into one folder.

Edit MigrateSchema_Oracle.bat according to your environment and Oracle server details, e.g. provide the path to the Oracle Instant Client folder. More details can be found inside the .bat file.

Run the batch file in a command prompt (NOT inside sqlplus).  This creates a new user called "TargetDatabase". It will have the same tables as the SourceDatabase, but without any data rows. The batch file will also creates some additional SQL files in this process for the purpose of migrating the source database structure. They do not need to be run manually.

Verify that the target database exits with the same tables as the source database and is empty. For instance, you can do this in sqlplus by executing the commands below.

First connect to the new user. You may need to change the password and the service name FREEPDB1 to match your environment.

CONNECT "TargetDatabase"/P455w07d@FREEPDB1;

Then run this command to list the tables. The database name needs to be in full capital:

select object_name from dba_objects where upper(owner)='TARGETDATABASE' and object_type='TABLE';

And run this command to count the number of data rows in a table:

SELECT COUNT(*) FROM "Users";

Run the subsetter

The commands below run the subsetter. If the commands cannot run successfully, you may need to work through the connection strings section below to configure them to your environment.

Click on the sections below to view the example command lines:

./subsetter.exe `
--database-engine oracle `
--source-connection-string 'DATA SOURCE=localhost:1521/FREEPDB1;USER ID="SourceDatabase";PASSWORD=P455w07d;' `
--target-connection-string 'DATA SOURCE=localhost:1521/FREEPDB1;USER ID="TargetDatabase";PASSWORD=P455w07d' `
--starting-table '"Users"' `
--filter-clause '"OrgId" = 1'

subsetter.exe
--database-engine oracle ^
--source-connection-string "DATA SOURCE=localhost:1521/FREEPDB1;USER ID=""SourceDatabase"";PASSWORD=P455w07d;" ^
--target-connection-string "DATA SOURCE=localhost:1521/FREEPDB1;USER ID=""TargetDatabase"";PASSWORD=P455w07d" ^
--starting-table """Users""" ^
--filter-clause """OrgId"" = 1"

#!/usr/bin/env bash
./subsetter \
--database-engine oracle \
--source-connection-string "DATA SOURCE=localhost:1521/FREEPDB1;USER ID=""SourceDatabase"";PASSWORD=P455w07d;" \
--target-connection-string "DATA SOURCE=localhost:1521/FREEPDB1;USER ID=""TargetDatabase"";PASSWORD=P455w07d" \
--starting-table "\"Users\"" \
--filter-clause "\"OrgId\" = 1"

Connection strings

You may need to change the connection strings to suit your environment. The connection strings identify the source and target database.

Change PASSWORD to match that of your own.

Use DATA SOURCE to specify the Oracle server containing the database. In the example above we are using localhost to connect to an instance running on the same computer that is running the subsetter. 

We have further advice on connection strings in our troubleshooting and known limitations section. There is also a lot of information online about connection strings.

Output from the command

The command will produce detailed output similar to that shown below. The details may vary. Check that the last line of output says Subsetting completed.

2023-11-02 13:53:11.061 +00:00 [INFO] Subsetter has started in 'Release' mode. Log folder is C:\ProgramData\Red Gate\Logs\TDM\Subsetter.
2023-11-02 13:53:11.158 +00:00 [INFO] Validating subsetting configuration data...
2023-11-02 13:53:11.756 +00:00 [INFO] Found starting table 'Users' with 12 row(s) in '' database on 'localhost:1521/FREEPDB1' server
2023-11-02 13:53:11.767 +00:00 [INFO] Applying filter clause '"OrgId" = 1' to starting table 'Users' produced 5 row(s).
2023-11-02 13:53:11.769 +00:00 [INFO] Completed initial validation of subsetting configuration data in 00h 00m 00s.610ms
2023-11-02 13:53:11.782 +00:00 [INFO] Application Insights telemetry is disabled and events will not be sent.
2023-11-02 13:53:11.783 +00:00 [INFO] Running Oracle database table extraction using '' database on 'localhost:1521/FREEPDB1' server...
2023-11-02 13:53:13.120 +00:00 [INFO] Successfully extracted 4 table(s) (with 4 relationships) from '' database on 'localhost:1521/FREEPDB1' server in 00h 00m 01s.336ms.
2023-11-02 13:53:13.123 +00:00 [INFO] Starting to build database migration model from source '' database on 'localhost:1521/FREEPDB1' server...
2023-11-02 13:53:13.130 +00:00 [INFO] Successfully built database migration model in 00h 00m 00s.007ms for starting tables 'Users' and clause '"OrgId" = 1' with 4 step(s)
2023-11-02 13:53:13.131 +00:00 [INFO] Running Oracle database table extraction using '' database on 'localhost:1521/FREEPDB1' server...
2023-11-02 13:53:13.861 +00:00 [INFO] Successfully extracted 4 table(s) (with 4 relationships) from '' database on 'localhost:1521/FREEPDB1' server in 00h 00m 00s.730ms.
2023-11-02 13:53:13.863 +00:00 [INFO] Validating source '' database on 'localhost:1521/FREEPDB1' server and target '' database on 'localhost:1521/FREEPDB1' server structure...
2023-11-02 13:53:13.878 +00:00 [INFO] Completed validation of source '' database on 'localhost:1521/FREEPDB1' server and target '' database on 'localhost:1521/FREEPDB1' server structure in 00h 00m 00s.014ms...
2023-11-02 13:53:14.338 +00:00 [INFO] [Pre-Cleanup] Constraints successfully disabled on target '' database on 'localhost:1521/FREEPDB1' server. This is temporary and only during the duration of the subsetting operation (they will be restored at the end).
2023-11-02 13:53:14.342 +00:00 [INFO] Starting subset data creation in source '' database on 'localhost:1521/FREEPDB1' server (via temporary tables)...
2023-11-02 13:53:14.532 +00:00 [INFO] Successfully created 4 temporary tables to hold the subset results in source '' database on 'localhost:1521/FREEPDB1' server in 00h 00m 00s.190ms.
2023-11-02 13:53:14.534 +00:00 [INFO] Starting subset data copy from 4 temporary tables in source '' database on 'localhost:1521/FREEPDB1' server to target '' database on 'localhost:1521/FREEPDB1' server...
2023-11-02 13:53:14.577 +00:00 [INFO]   RG1TEMP_Users    --> Users    : 5 row(s)
2023-11-02 13:53:14.603 +00:00 [INFO]   RG1TEMP_Posts    --> Posts    : 4 row(s)
2023-11-02 13:53:14.625 +00:00 [INFO]   RG1TEMP_Comments --> Comments : 2 row(s)
2023-11-02 13:53:14.641 +00:00 [INFO]   RG1TEMP_Orgs     --> Orgs     : 1 row(s)
2023-11-02 13:53:14.643 +00:00 [INFO] Subset complete. Successfully copied 4 subset tables from source '' database on 'localhost:1521/FREEPDB1' server into target '' database on 'localhost:1521/FREEPDB1' server in 00h 00m 00s.108ms.
2023-11-02 13:53:14.674 +00:00 [INFO] [Post-Cleanup] Constraints successfully re-enabled on target '' database on 'localhost:1521/FREEPDB1' server.
2023-11-02 13:53:15.092 +00:00 [INFO] [Post-Cleanup] Successfully deleted 4 temporary tables on source '' database on 'localhost:1521/FREEPDB1' server as they are no longer needed.
2023-11-02 13:53:15.209 +00:00 [INFO] Subsetting completed in 00h 00m 03s.427ms.

Check the data in the target database

Check the contents of the tables in the target database. You should find that each table contains some data rows, but fewer than in the source database.

Congratulations! This shows that the subset has been created successfully.

Further examples

The documentation on configuration files explains the formats and provides worked examples of their use. 

If you want to experiment with different filter clauses, please checkout subsetting example filter clauses. You might find it helpful to apply --target-database-write-mode Overwrite so that you can reuse the target database which has now been written to.

Learning more about how the subsetter works

Now that you have carried out the worked example, you might like to understand more about how the subsetter works. Please consult the page Using the worked example to understand the subsetter. This page uses the output and the commands from the worked example to explain how the subsetter produces a referentially intact subset.