DBMS_DATAPUMP import demo

We're currently looking at creating a repository of schema versions to be able to quickly deploy them to other systems - we want to build all of this in plsql so we needed to script up a way of doing datapump extracts. To keep thing simpler we would then store the extracts as schemas rather than dumpfiles.

So how do we do this?

Well to create these schema copies and store them in the database we have to use impdp - expdp can only extract to a file. So we have to do an impdp and 'pull' the schemas in to create the copies.

This is going to be easier if i do an example i think - so lets create a simple schema that we want to 'store' in our repository - here's the SQL for that.

create user dpdemo identified by dpdemo;
grant create session,resource, unlimited tablespace to dpdemo;
create table dpdemo.demo(col1 number);
create index dpdemo.demoidx on dpdemo.demo(col1);

Now comes the clever bit - we create a database link from our 'schema repository' database to wherever the schema we want to duplicate is (this could be a loopback link to the same database) .



create database link demo connect to user identified by password using 'DBNAME';

Once that link is in place we then just need to build the plsql - which i have here:

 DECLARE
  l_dp_handle      NUMBER;
  l_last_job_state VARCHAR2(30) := 'UNDEFINED';
  l_job_state      VARCHAR2(30) := 'UNDEFINED';
  l_sts            KU$_STATUS;
  v_job_state      varchar2(4000);
BEGIN
  l_dp_handle := DBMS_DATAPUMP.open(operation   => 'IMPORT',
                                    job_mode    => 'SCHEMA',
                                    remote_link => 'DEMO',
                                    version     => 'LATEST');
  DBMS_DATAPUMP.add_file(handle    => l_dp_handle,
                         filename  => 'test.log',
                         directory => 'DATA_PUMP_DIR',
                         filetype  => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                         reusefile => 1);
  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle, 'SCHEMA_LIST', '''DPDEMO''');
  DBMS_DATAPUMP.METADATA_FILTER(l_dp_handle,
                                'EXCLUDE_PATH_EXPR',
                                'IN (''INDEX'', ''SYNONYMS'',''GRANTS'',''STATISTICS'')');
  DBMS_DATAPUMP.METADATA_REMAP(l_dp_handle,
                               'REMAP_SCHEMA',
                               'DPDEMO',
                               'REL10A');
  DBMS_DATAPUMP.start_job(l_dp_handle);
  DBMS_DATAPUMP.WAIT_FOR_JOB(l_dp_handle, v_job_state);
  DBMS_OUTPUT.PUT_LINE(v_job_state);
END;
/

So what is this doing?

1)It says i want to do a schema level import using the DEMO database link
2) I want you to log everything to test.log
3) I only want the DPDEMO schema
4) I'm excluding some things (just to show how this can be done)
5) i want this copied schema to now be called REL10A

And there we go - the job runs fine and creates this log file on the server:

Starting "USER"."SYS_IMPORT_SCHEMA_01":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "REL10A"."DEMO"                                  0 rows
Job "USER"."SYS_IMPORT_SCHEMA_01" successfully completed at 13:44:30