Oracle 12c datapump have following clauses, which we would discuss one by one:-
EXCLUDE – This is used to filter the metadata from the export operation. This is more like a where clause which you can use to exclude any object or schema from the export job.
Example if you want a full database export except 1 schema (say TEST), then:-
|
1
|
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp FULL=YES EXCLUDE=SCHEMA:"='TEST'" LOGFILE=test.log
|
Similarly suppose you want to exclude all indexes from a schema export and the condition is to export only indexes that starts with TEST_%, then:-
|
1
|
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST EXCLUDE=INDEX:"LIKE 'TEST_%'" LOGFILE=test.log
|
So using this clause you can exclude any specific content from the dump, try and explore this option more. To get a list of available objects that can be excluded query the OBJECT_PATH column of DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS views.
INCLUDE – Exactly opposite to EXCLUDE clause, this is used when you explicitly want to export, say only some tables, indexes along with views, packages in a schema dump, so only the the object types explicitly mentioned in the include clause would be imported and nothing else.
|
1
|
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST INCLUDE=TABLE:"IN ('EMP', 'DEPT')" INCLUDE=PROCEDURE INCLUDE=INDEX:"LIKE 'TEST%'" LOGFILE=test.log
|
So the above expdp command would only export tables EMP & DEPT, PROCEDURE and all indexes starting with TEST% no other objects of TEST schema would be exported.
QUERY – This clause allows you to filter the data that gets exported, think of it as EXCLUDE & INCLUDE clause but with more control over filtering, thus this is more like a WHERE clause of the expdp command.
When the QUERY parameter is specified for a table, Data Pump uses external tables to unload the target table.
It accepts values in the form:- QUERY = [schema.][table_name:] query_clause
|
1
|
expdp user/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp SCHEMAS=TEST QUERY=TEST.EMP:"WHERE DEPT_ID > 90 AND SAL > 90000" LOGFILE=test.log
|
Above command would export all tables of TEST schema but only specific records of EMP table.
CLUSTER – This is used in RAC env. and used to spread the worker process on other oracle instances to utilize RAC resources for datapump jobs. Continue reading All Datapump Options →
