First page Back Continue Last page Graphics
…Data Pump Features…
Filtering during export and import
- Data, metadata or both
- Filter by object type, name
- Wildcard support
- See INCLUDE and EXCLUDE
- Filter data
- Supports WHERE, ORDER BY, more
- See QUERY
Notes:
Datapump import and export utilities provide many techniques for filtering, i.e. what gets imported or exported. These include:
Using CONTENT parameter to import or export just the data, just the metadata, or both.
Using INCLUDE and / or EXCLUDE to control what type of objects are affected, and / or the names of objects affected by the operation. This is very powerful. For example, to exclude all of Dave’s objects, code
exclude=schema:"='DAVE'“
To include only the table SCOTT.DEPT, code the parameter:
include=schema:"='SCOTT'",table:"='DEPT'"
Note that INCLUDE and EXCLUDE are mutually exclusive parameters.
Using the QUERY parameter to filter the data. For example, to load just the sales department, code:
query=scott.dept:"WHERE dname = 'SALES'"
However, be aware that this type of filtering – though powerful – can have side effects. For example, when I ran an import with the QUERY filter shown here, a subsequent foreign key constraint import failed because not all parent rows where loaded into the DEPT table.
Refer to Metalink Note Note:277010.1 for good examples of coding the QUERY parameter.