Monday, 20 January 2025

Using INCLUDE/EXCLUDE options in Oracle EXPDP/IMPDP

Within Oracle Datapump parameters, you can specify a set of objects to be included or excluded from an Export or Import operation, such as tables, indexes, grants, procedures.

EXCLUDE and INCLUDE and mutually exclusive parameters, that means you can only specify 1 of them at a time in EXPDP or IMPDP commands. Below shown in the syntax,

EXCLUDE = object_type[:name_clause] [, ...]
INCLUDE = object_type[:name_clause] [, ...]

Examples:

expdp ... SCHEMAS=scott EXCLUDE=SEQUENCE, TABLE:\"IN ('EMP', 'DEPT')\"
impdp ... SCHEMAS=scott INCLUDE=FUNCTION, PACKAGE, TABLE:\"= 'EMP'\"

You can specify multiple objects in 1 go and also use variations in that. In the above example, EXPDP will export entire SCOTT schema but will exclude all sequence and tables EMP, DEPT specified in TABLE clause. Similarly, IMPDP will include just Functions, Packages, and EMP table from SCOTT schema.
Note: Use space carefully & place escape characters on the command line as shown in the examples. OR you will get error and execution will fail. 

Let's see some more examples of variations we can use.

Using SQL Operators:


We can specify the entire object type OR filter out objects within some type.

EXCLUDE=SEQUENCE

or:

EXCLUDE=TABLE:"IN ('EMP', 'DEPT')"

or:

EXCLUDE=INDEX:"= '<INDEX_NAME>'"

or:

INCLUDE=PROCEDURE:"LIKE 'MY_PROC_%'"

or:

INCLUDE=TABLE:"> 'E'"

Double quotes and single quotes usage:


The name clause must be enclosed in double quotation marks. The single-quotation marks are required to delimit the name strings.

INCLUDE=TABLE:"IN ('EMP', 'DEPT')"

Using Parameter File:


Parameter file is helpful to simply things and not mix everything in command line. Rather specify your options in parameter file and call that in EXPDP command.

Parameter file: exp.par
-------------------------
DIRECTORY = my_dir
DUMPFILE  = exp_tab.dmp
LOGFILE   = exp_tab.log
SCHEMAS   = scott
INCLUDE   = TABLE:"IN ('EMP', 'DEPT')"


expdp system/password parfile=exp.par

Using the same filter name for an object type more than once:


In parameter file,

INCLUDE=TABLE:"LIKE '%E%'"
INCLUDE=TABLE:"LIKE '%P%'"

Exporting or Importing a large number of objects:


If a large number of objects needs to be exported (or imported), it is possible that an internal buffer limit is exceeded (e.g. for the parameters INCLUDE or TABLES). If that happens it will cause the Data Pump job to abort with an error such as: ORA-06502 (PL/SQL: numeric or value error: character string buffer too small). This happened especially in Oracle10g Release 1 where the value for the internal buffer was set to 3000 bytes. With the fix for Bug 4053129 "EXPDP fails with ORA-39125 ORA-6502 on large list of table names" (not a public bug; fixed in 10.1.0.5.0 and 10.2.0.x), this value was increased to 4000 bytes.
When exporting a large number of objects, we recommend to make use of a table inside the database that contains the names of the objects.
Example:

-- create a table that contains the names of the objects:

CONNECT scott/password
CREATE TABLE expdp_tab (owner VARCHAR2(30),
   object_name VARCHAR2(128), object_type VARCHAR2(19));
INSERT INTO expdp_tab VALUES ('SCOTT','EMP','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','DEPT','TABLE');
INSERT INTO expdp_tab VALUES ('SCOTT','BONUS','TABLE');
...
COMMIT;

-- run export DataPump job:

expdp system/password DIRECTORY=my_dir \
DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott \
INCLUDE=TABLE:\"IN \(SELECT object_name FROM scott.expdp_tab WHERE \
owner=\'SCOTT\' AND object_type=\'TABLE\'\)\"

Excluding Constraints:


-- Exclude all nonreferential constraints, except for 
-- NOT NULL constraints and any constraints needed for 
-- successful table creation and loading:

EXCLUDE=CONSTRAINT

-- Exclude referential integrity (foreign key) constraints:

EXCLUDE=REF_CONSTRAINT

Excluding Schema/User:


EXCLUDE=SCHEMA:"='SCOTT'"

Please remember there's a similar INCLUDE option for every EXCLUDE so if I am not specifying in examples, you please keep it in mind.

I hope this helps !!

No comments:

Post a Comment