Schema Refreshes using DBMS_DATAPUMP

DBMS_DATAPUMP exposes all the functionality available via the impdp and expdp commands available from a command prompt. Most of the time, access is limited to these commands, because they are owned by the 'oracle' user. However, this functionality can be exposed, to users and external programs without forcing them into command-line tools.

Use Case

Developers are requesting schema refreshes from production all the time and complaining that they aren't fast enough. It's also a time sink, even for the most junior DBA. This is a case for automation, but how?

Prerequistes
Create a schema specific for containing objects necessary for these types of self-service operations. I like 'MYDBA' (Q: Can you do this? A:Why don't you ask MYDBA.) or can you give this schema a character name, like Orko.(exec ORKO.REFRESH_SCHEMA())

Additionally, a database link is created from the refresh target, to the source. Ideally, this will be a private database link. In my code, the name of the database and the database link are the same, for reasons you'll see later.

Privileges

Before we get started with PL/SQL, we need to know what privileges are necessary. Fortunately, it's a pretty small list: EXP_FULL_DATABASE, IMP_FULL_DATABASE, CREATE TABLE. That's it.

Parameters
The code below requires three input parameters:

• src_db is the name of the database link used to pull data from as part of refresh. 
• src_schema is the name of the schema used as the source. 
• tgt_schema  is the schema being refreshed. 

Code / Gist

This is what you came for, right? As you can see from the snippet below, the REMAP_SCHEMA, REMAP_TABLESPACE are used, just as you'd expect them to be used from the command line or par file.