Table level data pump imports jobs are probably
the most often utilized mode. It is very easy to
think in terms of tables when working with data.
Table level mode just seems to be the natural
granularity of choice. Return to the prior
example of the developer working on code who
needs the ability to refresh those tables
between runs. The DBA could either import just
the tables that developer needs for that
programming task, or better yet, the DBA could
permit and
instruct the developer to import the tables
being worked upon. Either way, the data pump
import job would work in table mode and for the
tables requested, as shown here.
C:\> impdp bert/bert
directory=data_pump_dir dumpfile=multi_table.dmp
tables=movies.customer,movies.employee
Note that the table level mode data pump imports
have to be sourced from but one schema, or this
will be the following error.
C:\> impdp bert/bert
directory=data_pump_dir dumpfile=multi_user.dmp
tables=movies.customer,movies.employee,bert.junk
Import: Release 11.1.0.6.0
- Production on Saturday, 28 June, 2008
14:19:51
Copyright (c) 2003, 2007,
Oracle.
All rights reserved.
Connected to: Oracle
Database 11g
Enterprise
Edition Release 11.1.0.6.0 - Production
With the Partitioning,
OLAP, Data Mining and Real Application
Testing options
UDI-00012: table mode
imports only allow objects from one schema
Import Data Subsets
This is probably the most powerful and useful
aspect of the data import process, and yet it
remains highly underutilized. Say that one wants
to load data from a table while using a filter
upon the rows being inserted. That is easily
accomplished via a normal SELECT command’s WHERE
clause placed in the
query parameter passed to the
import process. So, as done before, one could
easily import only those customers who live in Texas as follows:
C:\> impdp bert/bert
directory=data_pump_dir dumpfile=all_of_movies.dmp
schemas=movies query=movies.customer:\"where
state='TX'\"
That seems easy enough, but there is a small
catch. The QUERY clause is applied to all the
tables in the import set, so
all the tables better have the columns
referenced by that WHERE clause. A common
example would be a schema table design where
each table contains a last modified date column.
If only records in that schema which had been
modified within the past three months should be
loaded, here is the data pump export command for
that:
C:\> impdp bert/bert
directory=data_pump_dir dumpfile=all_of_movies.dmp
schemas=movies query=\"where last_mod_date
is not null and last_mod_date > SYSDATE-90\"
But unlike the export, the data pump import does
not offer a sample method, so there is really no
way to externally read and filter the dump file
to try to filter the input or the create more
complex filtering scenarios. So plan
accordingly.
