Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle Import Table Level
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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 - 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 - 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.

r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.