Dr. Dobb's is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Channels ▼


Databases and Refreshing Test Data

Gregory Williams is a long-time database administrator.

I firmly believe that most Oracle database shops should have both a Test and a Production database. Of course, this also means that you also need to keep them in sync by refreshing your Test data on a regular basis -- a process which begs for automation.

When I decided to automate refreshing my Test database with data from Production data, I started by googling the Internet and discovered that most of the information I found suggested doing a full export/import of the Production database into Test database, or creating a clone of the Production database and recreating the Test database. These procedures assumed that you have at least the same amount of space on your test server as you do on your production server. In my case, I have a lot less space on my test server then I have on my production server; therefore, neither one of these procedures will work for me. Thanks to Oracle's Data Pump technology, I can export only the data that I need from Production into my Test database. For instance, I have videos stored in my Production database, which takes up a lot space, which I do not need in my Test database. The other feature that Data Pump has that's not in the old export/import technology is that it lets you export from a remote server. This is the main reason that I can automate my refresh test data process.

A new Oracle technology that I used is DBMS_SCHEDULER which let me create scheduled chains and scheduled programs. These technologies are all put together and run within Oracle's database using PLSQL packages and a lot of "execute immediate" functions. In this article, I show how to automate the refreshing of Test database with data from their Production database using the following Oracle technologies:

  • Data Pump
    • API -- export
    • API -- import

    • scheduled chains
    • scheduled programs
    • scheduled jobs

    • Dynamic SQL (execute immediate)

In this article, I first list the steps involved, then explain each of the steps, and finally show how DBMS_SCHEDULER is used to execute each step as a chain.

The following steps automatically execute the Refresh Test Data procedure. All of these steps are accessed from within a PLSQL package -- SCHEMA_REFRESH -- except the last one which is run directly from the DBMS_SCHEDULER package. This PLSQL package is located in the Test database on the Test server:

  1. Export Prod Database
  2. Clear Schemas
  3. Import Prod Dump File
  4. Cleanup Test Database
  5. Send email

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.