So, the World Cup is in full swing.
Now the lesser teams have fallen by the wayside ( England), we can get on with enjoying a feast of footie.
As well as a glut of goals, the current tournament has given us a salutory reminder of the importance of diet for elite athletes.
After predictably (and brilliantly) destroying England single-handedly, Luis Suaraz found himself a bit peckish and nipped out for an Italian. Now the whole world seems to be commenting on his eating habits.
Like Luis, you may find yourself thinking that you’ve bitten off more than you can chew when confronted by DBMS_DATAPUMP.
The documentation does offer some help…to an extent. However, the whole thing can seem a bit fiddly, especially if you’re used to the more traditional command-line interface for Datapump.
What follows is a tour through DBMS_DATAPUMP based on my own (sometimes painful) experience, broken down into bite-sized chunks.
Much of the functionality to filter object types and even data is common to both Exports and Imports.
So, the approach I’ve taken is to cover the Export first, with a view to finally producing a Full Database export.
I’ve then used the Import process against this to demonstrate some of the package’s filtering capabilities.
So, what’s on the menu today ?
- Privileges required to run a DBMS_DATAPUMP job from your current schema and for the whole database
- Running a consistent export
- Running datapump jobs in the background
- Monitoring running jobs
- Importing from one schema to another
- Specifying the types of objects to include in Exports and Imports
- Specifying subsets of data
- DDL only Jobs
- How to Kill a Datapump Job
The full code examples have all been written and tested on Oracle XE 11gR2.
I’ve tried to maximise the use of in-line hard-coded values and minimise the number of variables in an attempt to make the code easier to follow.
Also, in these examples I’ve made use of the default DATA_PUMP_DIR directory object, but you can use any directory object to which you have the appropriate privileges.
For dessert, there are a couple of other DBMS_DATAPUMP features that I have found useful that are specific to Enterprise Edition ( in one case, with the Partitioning Option) ;
- Including specific table partitions
- Parallel processing
So, a fair bit to get through then. I hope you have an apetite…
Privileges required for using DBMS_DATAPUMP
Obviously, the first thing you need is execute privileges on DBMS_DATAPUMP. By default, this is granted to PUBLIC, although the more security conscious DBAs will have instituted more rigorous controls over just who gets access to this package.
The other thing you’ll need is READ and WRITE privileges on a database Directory Object.
DATA_PUMP_DIR
The DATA_PUMP_DIR directory object is created by default (but any old Directory Object will do)…
select directory_path from dba_directories where directory_name = 'DATA_PUMP_DIR' / DIRECTORY_PATH -------------------------------------------------------------------------------- /u01/app/oracle/admin/XE/dpdump/
The HR user, for example, would need to be granted…
grant read, write on data_pump_dir to hr /
That’s pretty-much it. With just these privileges( along with those for creating/altering the relevant object types), you can run DBMS_DATAPUMP to export/import objects and data in your current schema.
Once again, assuming we’re connected as HR :
set serveroutput on size unlimited
declare
--
-- Just export the current schema (HR)
--
l_dph number; -- The datapump job handle
l_status varchar2(4000); -- terminating status of the job
begin
-- create the job...
l_dph := dbms_datapump.open
(
operation => 'EXPORT',
job_mode => 'SCHEMA', -- schema level export will use the current user by default
job_name => 'HR_SCHEMA_EXPORT' -- appropriate job name
);
-- Specify the name and location of the export dump file we want to create...
dbms_datapump.add_file
(
handle => l_dph,
filename => 'hr_export.dmp',
directory => 'DATA_PUMP_DIR', -- can use any database directory object
filetype => dbms_datapump.ku$_file_type_dump_file,
reusefile => 1 -- if this file already exists, overwrite it
);
-- ...and a log file to track the progress of the export
dbms_datapump.add_file
(
handle => l_dph,
filename => 'hr_export.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
-- Kick off the export
dbms_datapump.start_job( handle => l_dph);
-- ...and wait for the job to complete
dbms_datapump.wait_for_job( handle => l_dph, job_state => l_status);
dbms_output.put_line('Job done - status '||l_status);
end;
/
It’s probably worth noting that dbms_datapump jobs contain four main components as a rule.
First DBMS_DATAPUMP.OPEN creates a kind of container to hold the definition of the job.
Next, you specify any particular requirements for the job. This must always include reference to a dump file.
Then, use DBMS_DATAPUMP.START_JOB to kick-off the job you’ve defined.
Finally, you can either wait for the job to finish (as in this case), or leave it to run in the background. We’ll come onto background execution in a bit.
Roles required for Full Export/Import
If you want the God-Like powers to export/import the entire database then you will need the roles :
- DATAPUMP_EXP_FULL_DATABASE
- DATAPUMP_IMP_FULL_DATABASE
These roles are granted to the DBA role and the SYS user by default.
Incidentally, the fact that roles play quite a significant part in DBMS_DATAPUMP priviliges means that, if you do intend to wrap this functionality in a package, it would probably be wise to create it with Invoker Rights – e.g.
create or replace package my_datapump_package
authid current_user as
...
Now that’s all sorted…
set serveroutput on size unlimited
declare
--
-- Do a no-nonsense full export
--
l_dph number;
l_status varchar2(4000);
begin
l_dph := dbms_datapump.open
(
operation => 'EXPORT',
job_mode => 'FULL',
job_name => 'MIKE_FULL_EXPORT'
);
-- Specify the dump file we're going to create
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file,
reusefile => 1
);
-- ... and a log file
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
-- Now press the big red shiny button...
dbms_datapump.start_job( handle => l_dph);
-- wait around for a bit...
dbms_datapump.wait_for_job( handle => l_dph, job_state => l_status);
dbms_output.put_line('Job done - status '||l_status);
end;
/
Pretty much the same as with the schema export we’ve already done. However, this time, we’ve specified the job_mode as FULL rather than SCHEMA.
With this script saved as full_exp_simple.sql…
SQL> @full_exp_simple.sql Job done - status COMPLETED PL/SQL procedure successfully completed. SQL>
Depending on the size of your database ( and the capacity of the server you’re running on), this may take a while.
When it’s all finished, you can wander over to the DATA_PUMP_DIR on the OS and you’ll see the fruit of your labours…
cd /u01/app/oracle/admin/XE/dpdump ls -l my_full_export.* -rw-r----- 1 oracle dba 321515520 Jun 18 19:55 my_full_export.dmp -rw-r--r-- 1 oracle dba 84957 Jun 18 19:55 my_full_export.log
If we look at the logfile, we can see it starts like this :
Starting "MIKE"."MIKE_FULL_EXPORT": Estimate in progress using BLOCKS method... Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 349.9 MB Processing object type DATABASE_EXPORT/TABLESPACE ...
… and ends with …
. . exported "SYSTEM"."SQLPLUS_PRODUCT_PROFILE" 0 KB 0 rows Master table "MIKE"."MIKE_FULL_EXPORT" successfully loaded/unloaded ****************************************************************************** Dump file set for MIKE.MIKE_FULL_EXPORT is: /u01/app/oracle/admin/XE/dpdump/my_full_export.dmp Job "MIKE"."MIKE_FULL_EXPORT" successfully completed at 19:55:33
We now have an export. If, like me, you’ve just run this from a database where you know that there are no other active sessions, then you should have a nice consistent export. Unfortunately, this isn’t always the case…
Making your Export Read Consistent…and other tweaks
To adress this issue of consistency, we’ll need to change our script a bit. Whilst we’re at it, it would also be nice to have some record in the logfile of when the job started so we can work out how long it actually ran for.
Finally, we’d like to be able to run the export in the background so we don’t have to hang around waiting for it.
Consistent = Y – the DATAPUMP equivalent
Those of you old enough to remember the original export utility will recall that you could ensure that data in an export was referrentially consistent by the simple expedient of specifying consistent = Y in the exp command.
The equivalent in DBMS_DATAPUMP is to specify a value for FLASBACK_SCN.
NOTE – in order for this to work, your database must be running in ARCHIVELOG mode. This is especially relevant if you’re playing along on XE, which runs in NOARCHIVELOG by default.
To check the current status of archiving on the database :
select log_mode from v$database / LOG_MODE ------------ ARCHIVELOG
If the query comes back NOARCHIVELOG then you need to enable archiving.
To do this, you need to connect as SYSDBA, shutdown and then mount the database, before starting archiving and then re-opening the database.
These steps can be achieved as follows once connected to the database as sysdba :
shutdown immediate; startup mount; alter database archivelog / alter database open /
NOTE – these steps are intended if you are messing around in your own database ( e.g. XE on a laptop, like I am here). If you’re on a controlled environment, then you need to get your friendly neighbourhood DBA to do the honours.
Assuming the database is now in archivelog mode, the next thing we need to do is find SCN that we can pass to DBMS_DATAPUMP for it to use as a reference point for what we mean by consistent.
The SCN – System Change Number – is incremented every time a transaction completes in the database.
A quick demonstration is probably in order….
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2774525
SQL> create table snacks( snack_name varchar2(30), description varchar2(4000));
Table created.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2774562
SQL> insert into snacks( snack_name, description)
2 values( 'CHIELLINI', 'Lite bite')
3 /
1 row created.
SQL> commit;
Commit complete.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2774581
SQL> drop table snacks purge;
Table dropped.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2774608
SQL>
The fact that the SCN increments by more than one after each transaction completes in this session is explained by the transactions being run by the oracle background processes on my database.
Anyway, if we do want to find the current SCN, or even an SCN for a given time and date, we can simply use the
SQL TIMESTAMP_TO_SCN function :
select timestamp_to_scn( systimestamp)
from dual;
TIMESTAMP_TO_SCN(SYSTIMESTAMP)
------------------------------
2647783
SQL>
In order to pass this information to DBMS_DATAPUMP, we need to use the SET_PARAMETERS procedure.
This should do the trick :
...
dbms_datapump.set_parameter
(
handle => the_datapump_job_handle,
name => 'FLASHBACK_SCN',
value => timestamp_to_scn( systimestamp)
);
...
Adding a message to the logfile
Next on our to-do list to improve our export is a message in the logfile recording when the export job started.
Oh look, there’s a package member called LOG_ENTRY. I wonder if that will help …
...
dbms_datapump.log_entry
(
handle => the_datapump_job_handle,
message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
);
...
Incidentally, we could include the SCN we’re specifying in the message as well ( although it would need to be captured in a variable before use in both the SET_PARAMETER call above and the LOG_ENTRY call).
One point to note – any entry in the logfile that results from a call to this procedure always starts with “;;;”.
Running the job in the background
Just in case you don’t fancy the idea of hanging around waiting for the job to finish, you can replace the call to WAIT_FOR_JOB with this…
... dbms_datapump.detach( handle => the_datapump_job_handle); ...
The New, Improved Datapump Export Script
If we now apply all of these changes to our original script, it should look something like ….
set serveroutput on size unlimited
declare
l_dph number;
l_scn number; -- The SCN from which the export will run
begin
-- setup the job
l_dph := dbms_datapump.open
(
operation => 'EXPORT',
job_mode => 'FULL',
job_name => 'MIKE_FULL_EXPORT'
);
-- define the files
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file,
reusefile => 1
);
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
--
-- Specify the SCN number to make sure that the export
-- is a consistent copy
--
l_scn := timestamp_to_scn(systimestamp);
dbms_datapump.set_parameter
(
handle => l_dph,
name => 'FLASHBACK_SCN',
value => l_scn
);
-- log the start time of the job
dbms_datapump.log_entry
(
handle => l_dph,
message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')||' for SCN '||l_scn
);
-- start the job
dbms_datapump.start_job( handle => l_dph);
-- ...and leave it to run
dbms_datapump.detach( handle => l_dph);
end;
/
Monitoring the job
The USER_DATAPUMP_JOBS view (there is also a DBA_ version available) contains details of any currently defined datapump jobs.
SQL> select job_name, state, attached_sessions 2 from user_datapump_jobs; JOB_NAME STATE ATTACHED_SESSIONS ------------------------------ ------------------------------ ----------------- MIKE_FULL_EXPORT EXECUTING 1 SQL>
Additionally, during the export, DATAPUMP will create a temporary table with the same name as the job.
In this table, you can see which object (if any) it’s currently working on…
select object_schema, object_name,
work_item,
to_char(last_update, 'hh24:mi:ss') last_updated
from mike_full_export
where state = 'EXECUTING'
and object_schema is not null
and object_name is not null
/
There is an example of how to get “real-time” updates in the Oracle Documentation.
Unfortunately, it relies on DBMS_OUTPUT so the messages don’t get echoed to the screen until after the job is completed.
The simplest way to find out what’s happening right now is to check the logfile.
On Linux, for example, you could simply do this :
cd /u01/app/oracle/admin/XE/dpdump tail -f my_full_export.log
If you haven’t got command line access to the database server, or simply prefer to keep everything in the database, then you could just create an external table based on the logfile. After all, you already have the required privileges on the directory …
create table datapump_log_xt
(
line number,
text varchar2(4000)
)
organization external
(
type oracle_loader
default directory data_pump_dir
access parameters
(
records delimited by newline
nologfile
fields terminated by whitespace
(
line recnum,
text position(1:4000)
)
)
location('')
)
reject limit unlimited
/
If we want to check progress, we can simply “tail” the file like this :
alter table datapump_log_xt location ('my_full_export.log')
/
select text
from datapump_log_xt
where line > ( select max(line) - 10 from datapump_log_xt)
order by line;
TEXT
--------------------------------------------------------------------------------
Processing object type DATABASE_EXPORT/CONTEXT
Processing object type DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
10 rows selected.
SQL>
So far, we’ve looked exclusively at Exporting everything either from a given schema or an entire database.
Datapump also allows you to filter the data. Generally speaking, these filters apply whether you are importing or exporting.
Importing data from one schema to another
One of the many reasons that you may want to take a DataPump export may be to refresh a development environment.
It’s possible that the schema you’re refreshing on the Development database has a different name from the one on your Production environment. Let’s say, for the sake of argument, that we have a HR_DEV user in our development environment…
create user hr_dev identified by pwd
/
grant connect, resource, unlimited tablespace,
create view, create sequence, create session,
alter session, create synonym, create table,
create procedure
to hr_dev
/
alter user hr_dev default tablespace users temporary tablespace temp
/
Fortunately, importing the HR objects in the export file into the HR_DEV user is fairly simple :
...
dbms_datapump.metadata_remap
(
handle => the_datapump_job_handle,
name => 'REMAP_SCHEMA'
old_value => 'HR'
value => 'HR_DEV'
);
...
Specifying schemas and object types
At this point, let’s say that we only want to import the HR schema. The rest of the objects in the export file aren’t really relevant to us.
Furthermore, let’s assume we only want to import Tables and Sequences as we’ll re-create all of our PL/SQL stored program units, views etc from source code files.
Filtering by schema
Allow me to introduce the METADATA_FILTER procedure :
...
dbms_datapump.metadata_filter
(
handle => the_datapump_job_handle,
name => 'SCHEMA_EXPR',
value => q'[= 'HR']'
);
...
Once again, not as painful as it might appear. However, it’s here where we begin to see the benefits of quote delimiters.
Filtering by object types
Here’s where things get a bit more interesting.
Remember the export logfile. At the beginning, there were a number of entries like :
Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER ...
These paths are the basis for how datapump determines what to export.
Fortunately they are stored in some publicly available views :
- DATABASE_EXPORT_OBJECTS
- SCHEMA_EXPORT_OBJECTS
- TABLE_EXPORT_OBJECTS
We’re doing a SCHEMA import so we can check to see that the relevant object_path is available to us by :
select object_path, comments
from schema_export_objects
where object_path in ('TABLE', 'SEQUENCE')
/
OBJECT_PATH COMMENTS
-------------------- --------------------------------------------------------------------------------
SEQUENCE Sequences in the selected schemas and their dependent grants and audits
TABLE Tables in the selected schemas and their dependent objects
The Path Map looks to be a flattened hierarchy ( possibly an XML representation). The point here is that, by specifying a node in this hierarchy, you can persuade DBMS_DATAPUMP to do your bidding.
As both TABLE and SEQUENCE are nodes in the object_path, we should be able to use that here…
...
dbms_datapump.metadata_filter
(
handle => the_datapump_job_handle,
name => 'INCLUDE_PATH_EXPR',
value => q'[IN ('TABLE', 'SEQUENCE')]'
);
...
This will give us everything at the level at and below the TABLE and SEQUENCE nodes. Therefore, you will also get INDEXES for the tables…as well as Triggers.
select object_path, comments from schema_export_objects where object_path like '%TABLE%TRIGGER%' / OBJECT_PATH COMMENTS ------------------------------ ------------------------------ SCHEMA_EXPORT/TABLE/TRIGGER Triggers TABLE/TRIGGER Triggers
Remember, we don’t want any PL/SQL program units, so we need to filter these out. Fortunately, calls to the METADATA_FILTER procedure seem to be addative. As TRIGGER appears below TABLE in the hierarchy, we can filter them out with an additional call to the procedure :
...
dbms_datapump.metadata_filter
(
handle => the_datapump_job_handle,
name => 'EXCLUDE_PATH_EXPR',
value => q'[= 'TRIGGER']'
);
...
The finished HR Import Script
Here it is then, a script to Import Tables and Sequences from HR to HR_DEV :
set serveroutput on size unlimited
declare
--
-- Import of HR objects to HR_DEV
-- Only importing Tables and Sequences
--
l_dph number;
l_predicate varchar2(4000);
begin
-- Setup the job "context" as usual
l_dph := dbms_datapump.open
(
operation => 'IMPORT',
job_mode => 'SCHEMA',
job_name => 'IMP_HR_DEV'
);
-- We only want objects in the HR schema
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'SCHEMA_EXPR',
value => q'[= 'HR']'
);
-- and then only Sequences and Tables
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'INCLUDE_PATH_EXPR',
value => q'[IN ('TABLE', 'SEQUENCE')]'
);
-- ...but no triggers...
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'EXCLUDE_PATH_EXPR',
value => q'[= 'TRIGGER']'
);
-- and we want to import these objects into the HR_DEV schema
dbms_datapump.metadata_remap
(
handle => l_dph,
name => 'REMAP_SCHEMA',
old_value => 'HR',
value => 'HR_DEV'
);
--
-- If we find a table that already exists in the target
-- schema then overwrite it..
--
dbms_datapump.set_parameter
(
handle => l_dph,
name => 'TABLE_EXISTS_ACTION',
value => 'REPLACE'
);
-- Now point to the export dump file to take this from
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file
);
-- ...and make sure we log what's going on...
dbms_datapump.add_file
(
handle => l_dph,
filename => 'hr_dev_imp.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
-- log the start time...
dbms_datapump.log_entry
(
handle => l_dph,
message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
);
-- start the job...
dbms_datapump.start_job( handle => l_dph);
-- and detach...
dbms_datapump.detach(handle => l_dph);
end;
/
Now to give it a whirl ( saved as hr_dev_imp.sql)….
SQL> @hr_dev_imp.sql PL/SQL procedure successfully completed. SQL>
Looking at the logfile, all appears well…
;;; Job starting at 18:15:29 Master table "MIKE"."IMP_HR_DEV" successfully loaded/unloaded Starting "MIKE"."IMP_HR_DEV": Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA . . imported "HR_DEV"."COUNTRIES" 6.367 KB 25 rows . . imported "HR_DEV"."DEPARTMENTS" 7.007 KB 27 rows . . imported "HR_DEV"."EMPLOYEES" 16.80 KB 107 rows . . imported "HR_DEV"."JOBS" 6.992 KB 19 rows . . imported "HR_DEV"."JOB_HISTORY" 7.054 KB 10 rows . . imported "HR_DEV"."LOCATIONS" 8.273 KB 23 rows . . imported "HR_DEV"."REGIONS" 5.476 KB 4 rows Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Job "MIKE"."IMP_HR_DEV" successfully completed at 18:16:15
If we now connect as HR_DEV and check the objects we have in our schema :
select object_name, object_type from user_objects order by object_type, object_name / OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ COUNTRY_C_ID_PK INDEX DEPT_ID_PK INDEX DEPT_LOCATION_IX INDEX EMP_DEPARTMENT_IX INDEX EMP_EMAIL_UK INDEX EMP_EMP_ID_PK INDEX EMP_JOB_IX INDEX EMP_MANAGER_IX INDEX EMP_NAME_IX INDEX JHIST_DEPARTMENT_IX INDEX JHIST_EMPLOYEE_IX INDEX JHIST_EMP_ID_ST_DATE_PK INDEX JHIST_JOB_IX INDEX JOB_ID_PK INDEX LOC_CITY_IX INDEX LOC_COUNTRY_IX INDEX LOC_ID_PK INDEX LOC_STATE_PROVINCE_IX INDEX REG_ID_PK INDEX DEPARTMENTS_SEQ SEQUENCE EMPLOYEES_SEQ SEQUENCE LOCATIONS_SEQ SEQUENCE COUNTRIES TABLE DEPARTMENTS TABLE EMPLOYEES TABLE JOBS TABLE JOB_HISTORY TABLE LOCATIONS TABLE REGIONS TABLE 29 rows selected. SQL>
Sequences are special
There is one thing to be aware of with importing sequences.
Most database objects ( procedure, packages, triggers, views etc), can be overwritten by using CREATE OR REPLACE.
Like tables, you cannot do this with Sequences.
Unlike tables, DBMS_DATAPUMP does not have an option to re-create existing sequences.
This means that, if we were to refresh the HR_DEV schema again using the same script, we’d be likely to run into a bit of a problem, as you can see from this log file entry :
... Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE ORA-31684: Object type SEQUENCE:"HR_DEV"."LOCATIONS_SEQ" already exists ORA-31684: Object type SEQUENCE:"HR_DEV"."DEPARTMENTS_SEQ" already exists ORA-31684: Object type SEQUENCE:"HR_DEV"."EMPLOYEES_SEQ" already exists ...
With this in mind, it’s probably a good idea to drop any sequences prior to refreshing with an import…
set serveroutput on size unlimited
begin
for r_seq in
(
select sequence_name
from dba_sequences
where sequence_owner = 'HR_DEV'
)
loop
--
-- check the sequence name is "clean"
--
if regexp_instr( replace( r_seq.sequence_name, '_'), '[[:punct:]]|[[:space:]]') > 0
then
raise_application_error( -20000, 'Sequence name contains dodgy characters.');
end if;
-- drop the sequence
execute immediate 'drop sequence hr_dev.'||r_seq.sequence_name;
dbms_output.put_line('Sequence '||r_seq.sequence_name||' dropped.');
end loop;
end;
/
If we run this we’ll get…
SQL> @drop_sequences.sql Sequence DEPARTMENTS_SEQ dropped. Sequence EMPLOYEES_SEQ dropped. Sequence LOCATIONS_SEQ dropped. PL/SQL procedure successfully completed. SQL>
Specifying a subset of data
Sometimes you don’t want to export/import everything. You might just want a few tables with a subset of data.
In our HR_DEV environment we want to focus on the EMPLOYEES table. We don’t want all of the rows – let’s just have one department…
Specifying which tables to use
Once again, METADATA_FILTER can be used here :
dbms_datapump.metadata_filter
(
handle => the_datapump_job_handle,
name => 'NAME_LIST',
value => q'['EMPLOYEES']',
object_path => 'TABLE'
);
NAME_LIST tells datapump to look for object names rather than in object type paths.
Specifying the object type path as TABLE means that datapump will only look for a table called EMPLOYEES and not any other type of object with the same name.
Getting a subset of data
Here, we need to use the DATA_FILTER procedure. Unlike it’s METADATA counterpart, you need to provide a syntactically correct predicate for it to work…
...
dbms_datapump.data_filter
(
handle => the_datapump_job_handle,
name => 'SUBQUERY',
value => 'where department_id = 20',
table_name => 'EMPLOYEES',
schema_name => 'HR'
);
...
The Data Sub-set Import Script
Before running this, I’ve taken the precaution of dropping all of the objects from the HR_DEV schema as I don’t want to run into any pesky constraint errors…
set serveroutput on size unlimited
declare
--
-- import Subset of employees into HR_DEV ( having cleared down the schema first)
--
l_dph number;
begin
l_dph := dbms_datapump.open
(
operation => 'IMPORT',
job_mode => 'SCHEMA',
job_name => 'IMP_EMPLOYEES'
);
-- We only want objects in the HR schema
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'SCHEMA_EXPR',
value => q'[= 'HR']'
);
-- only TABLES...
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'INCLUDE_PATH_EXPR',
value => q'[= 'TABLE']'
);
-- and then only the EMPLOYEES table
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'NAME_LIST',
value => q'['EMPLOYEES']',
object_path => 'TABLE'
);
-- without any triggers or ref constraints
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'EXCLUDE_PATH_EXPR',
value => q'[IN ('TRIGGER', 'REF_CONSTRAINT')]'
);
-- subset of EMPLOYEES
dbms_datapump.data_filter
(
handle => l_dph,
name => 'SUBQUERY',
value => 'where department_id = 20',
table_name => 'EMPLOYEES',
schema_name => 'HR'
);
dbms_datapump.metadata_remap
(
handle => l_dph,
name => 'REMAP_SCHEMA',
old_value => 'HR',
value => 'HR_DEV'
);
--
-- If we find a table that already exists in the target
-- schema then overwrite it..
--
dbms_datapump.set_parameter
(
handle => l_dph,
name => 'TABLE_EXISTS_ACTION',
value => 'REPLACE'
);
--
-- Now point to the export dump file to take this from
--
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file
);
--
-- ...and make sure we log what's going on...
--
dbms_datapump.add_file
(
handle => l_dph,
filename => 'hr_dev_employees_imp.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
-- log the start time...
dbms_datapump.log_entry
(
handle => l_dph,
message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
);
--
-- start the job...
--
dbms_datapump.start_job( handle => l_dph);
--
-- and detach...
--
dbms_datapump.detach(handle => l_dph);
end;
/
Notice that I’ve also specifically excluded the REF_CONSTRAINTS and TRIGGERS from the import.
Run this and we get the following output in the logfile :
;;; Job starting at 18:35:49 Master table "MIKE"."IMP_EMPLOYEES" successfully loaded/unloaded Starting "MIKE"."IMP_EMPLOYEES": Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA . . imported "HR_DEV"."EMPLOYEES" 16.80 KB 2 out of 107 rows Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Job "MIKE"."IMP_EMPLOYEES" successfully completed at 18:36:33
We can see that only the EMPLOYEES table and it’s associated indexes have been imported :
select object_name, object_type from user_objects order by object_type, object_name 4 / OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ EMP_DEPARTMENT_IX INDEX EMP_EMAIL_UK INDEX EMP_EMP_ID_PK INDEX EMP_JOB_IX INDEX EMP_MANAGER_IX INDEX EMP_NAME_IX INDEX EMPLOYEES TABLE 7 rows selected. SQL>
…and no Foreign Key constraints :
SQL> select table_name, constraint_name, constraint_type 2 from user_constraints; TABLE_NAME CONSTRAINT_NAME C ------------------------------ ------------------------------ - EMPLOYEES EMP_LAST_NAME_NN C EMPLOYEES EMP_EMAIL_NN C EMPLOYEES EMP_HIRE_DATE_NN C EMPLOYEES EMP_JOB_NN C EMPLOYEES EMP_EMP_ID_PK P EMPLOYEES EMP_EMAIL_UK U EMPLOYEES EMP_SALARY_MIN C 7 rows selected. SQL>
and only those EMPLOYEES in DEPARTMENT_ID 20…
select last_name, department_id from employees / LAST_NAME DEPARTMENT_ID ------------------------- ------------- Hartstein 20 Fay 20 SQL>
DDL_ONLY Datapump operations
Once again the DATA_FILTER procedure comes in here. However, this time, the call is a bit different :
... dbms_datapump.data_filter ( handle => the_datapump_job_handle, name => 'INCLUDE_ROWS', value => 0 ); ...
Here the value is effectively a boolean – 0 = false i.e. don’t include rows. The default is 1 – do include rows.
Incidentally, this time I’ve dropped the user HR_DEV altogether before importing as it will be re-created by the Import if it does not already exist.
This next script will import all of the DDL from HR to HR_DEV, but will not import any data…
set serveroutput on size unlimited
declare
--
-- Import of HR objects to HR_DEV
-- This time all DDL, but no data
--
l_dph number;
begin
l_dph := dbms_datapump.open
(
operation => 'IMPORT',
job_mode => 'SCHEMA',
job_name => 'IMP_DDL_HR_DEV'
);
-- We only want objects in the HR schema
dbms_datapump.metadata_filter
(
handle => l_dph,
name => 'SCHEMA_EXPR',
value => q'[= 'HR']'
);
-- but no data
dbms_datapump.data_filter
(
handle => l_dph,
name => 'INCLUDE_ROWS',
value => 0
);
--
-- and we want to import these objects into the HR_DEV schema
--
dbms_datapump.metadata_remap
(
handle => l_dph,
name => 'REMAP_SCHEMA',
old_value => 'HR',
value => 'HR_DEV'
);
-- If we find a table that already exists in the target
-- schema then overwrite it..
dbms_datapump.set_parameter
(
handle => l_dph,
name => 'TABLE_EXISTS_ACTION',
value => 'REPLACE'
);
-- Now point to the export dump file to take this from
dbms_datapump.add_file
(
handle => l_dph,
filename => 'my_full_export.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file
);
-- ...and make sure we log what's going on...
dbms_datapump.add_file
(
handle => l_dph,
filename => 'hr_dev_ddl_imp.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file,
reusefile => 1
);
-- log the start time...
dbms_datapump.log_entry
(
handle => l_dph,
message => 'Job starting at '||to_char(sysdate, 'HH24:MI:SS')
);
-- start the job...
dbms_datapump.start_job( handle => l_dph);
-- and detach...
dbms_datapump.detach(handle => l_dph);
end;
/
After running this we find that the HR_DEV user has been created. However, you will need to connect using the password of the HR user included in the export.
We can see now that all of the HR objects have been imported into HR_DEV :
select object_name, object_type from user_objects order by object_type, object_name / OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ ADD_JOB_HISTORY PROCEDURE COUNTRIES TABLE COUNTRY_C_ID_PK INDEX DEPARTMENTS TABLE DEPARTMENTS_SEQ SEQUENCE DEPT_ID_PK INDEX DEPT_LOCATION_IX INDEX EMPLOYEES TABLE EMPLOYEES_SEQ SEQUENCE EMP_DEPARTMENT_IX INDEX EMP_DETAILS_VIEW VIEW EMP_EMAIL_UK INDEX EMP_EMP_ID_PK INDEX EMP_JOB_IX INDEX EMP_MANAGER_IX INDEX EMP_NAME_IX INDEX JHIST_DEPARTMENT_IX INDEX JHIST_EMPLOYEE_IX INDEX JHIST_EMP_ID_ST_DATE_PK INDEX JHIST_JOB_IX INDEX JOBS TABLE JOB_HISTORY TABLE JOB_ID_PK INDEX LOCATIONS TABLE LOCATIONS_SEQ SEQUENCE LOC_CITY_IX INDEX LOC_COUNTRY_IX INDEX LOC_ID_PK INDEX LOC_STATE_PROVINCE_IX INDEX REGIONS TABLE REG_ID_PK INDEX SECURE_DML PROCEDURE SECURE_EMPLOYEES TRIGGER UPDATE_JOB_HISTORY TRIGGER USER_FK_TREE_VW VIEW 35 rows selected. SQL>
…and just to prove that we’ve imported no data at all…
SQL> select count(*) from regions;
COUNT(*)
----------
0
SQL> select count(*) from locations;
COUNT(*)
----------
0
SQL> select count(*) from departments;
COUNT(*)
----------
0
SQL> select count(*) from jobs;
COUNT(*)
----------
0
SQL> select count(*) from job_history;
COUNT(*)
----------
0
SQL> select count(*) from employees;
COUNT(*)
----------
0
SQL> select count(*) from countries;
COUNT(*)
----------
0
SQL>
Killing a runaway job
There will be times when things don’t quite work as expected. This is especially true if you’re playing around with Datapump parameter settings.
Just occasionally when testing the code in this post I’ve come across some variation on the theme of …
ERROR at line 1: ORA-31634: job already exists ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79 ORA-06512: at "SYS.DBMS_DATAPUMP", line 1137 ORA-06512: at "SYS.DBMS_DATAPUMP", line 5283 ORA-06512: at line 11
Yep, I’v fixed my runtime error, re-executed the scrpt and got this insistent little message.
The first thing to do when you get this ( especially if you’re running in the same session that you started the original job) is to logout and log back in again.
The second thing to do is to check to see if the job is still hanging about…
user select job_name, state from user_datapump_jobs /
If so, then you can attach to the job and stop it.
If I’m connected as the job owner and I’m only running the one job, the following script should do the trick…
set serveroutput on size unlimited
declare
l_dph number;
l_job_name user_datapump_jobs.job_name%type;
begin
select job_name into l_job_name from user_datapump_jobs;
dbms_output.put_line('Killing '||l_job_name);
l_dph := dbms_datapump.attach( job_name => l_job_name, job_owner => user);
dbms_datapump.stop_job( l_dph);
dbms_output.put_line('Job '||l_job_name||' terminated.');
exception
when no_data_found then
dbms_output.put_line('Job has already terminated.');
end;
/
This should work almost every time. On the off-chance it doesn’t, and the job does not appear in the USER_DATAPUMP_JOBS view anymore, then it’s possible that the temporary table created as part of the job has not been tidied up.
Remember, the temporary table is created in the job owner’s schema with the same name as the datapump job itself.
So, if I were to hit this problem whilst running the job IMP_HR_DEV, I could check :
select 1 from user_tables where table_name = 'IMP_HR_DEV' /
If the table does still exist and there is no entry in the _DATAPUMP_JOB view, we can simply drop it :
drop table imp_hr_dev /
This should finally persuade datapump that the job is not in fact running.
A couple of Enterprise Edition Features
For those of you on a richer diet, there are a couple of further morsels which may well be worthy of consideration.
If you want to speed up your Datapump job by making use of the CPU cores available you could try :
... dbms_datapump.set_parallel ( handle => the_datapump_job_handle, degree => 32 ); ...
If you’ve really pushed the boat out and have the partitioning option, you can tell datapump to take only specific partitions for tables.
For example, say you have a number of tables that are partitioned by date ranges.
The tables may be partitioned by quarter-year, with the partitions following a consistend naming convention (e.g. Q12014, Q22014 etc).
If we only want to export/import the latest partition for each of these tables( say Q22014), we can do something like this :
... for r_part_tabs in ( select table_name from user_tab_partitions where partition_name = 'Q22014' ) loop dbms_datapump.data_filter ( handle => the_datapump_job_handle, name => 'PARTITION_EXPR', value => q'[= 'Q22014']', table_name => r_part_tabs.table_name ); end loop ...
Despite having gorged ourselves on this feast of DBMS_DATAPUMP delights, it’s fair to say that I’ve by no means covered everything.
Doubtless you will want to do things with this API that I’ve not covered here. Indeed, you’ll probably also find better ways of implementing some of the same functionality.
In the meantime, I’m off for a digestif.
Bon Apetite.
