Thursday, January 29, 2009

ORA-31655: no data or metadata objects selected for job

Problem Description
You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp.
In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc)

Your parameter file is like below.
userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "

And you invoke expdp as
expdp parfile=d:\parfile.txt
from command line. But it fails with below message on my windows PC.
C:\>expdp parfile=d:\parfile.txt

Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01": parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
ORA-31655: no data or metadata objects selected for job
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 14:53:50

As you expected 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION' will be exported but not a single table is exported and error ORA-31655: no data or metadata objects selected for job returned.

Cause of the Problem
This problem happens because of multiple INCLUDE options was set in expdp. Note that multiple INCLUDE parameter can be given in expdp but I recommend not to do that because if multiple filters(EXCLUDE/INCLUDE) are specified , an implicit AND operation is applied to them.

In this example data pump interprets
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"  
include =TABLE:"LIKE 'CV%' "

as,
TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" AND TABLE:"LIKE 'CV%'"

As AND operation is applied, so all tables is filtered out (because no tables is there that starts with CV and between 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP') and no tables are exported.

A bit clear example I will show you.

Your parameter file is like below.
userid=smilebd/a
directory=d
dumpfile=b.dmp
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"
include =TABLE:"LIKE 'CV%' "

And you invoke datapump
C:\>expdp parfile=d:\parfile.txt

Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:54

Copyright (c) 2003, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SMILEBD"."SYS_EXPORT_SCHEMA_01": parfile=d:\parfile.txt
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SMILEBD"."ACCOUNT_GROUP" 11.75 KB 132 rows
Master table "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SMILEBD.SYS_EXPORT_SCHEMA_01 is:
G:\B.DMP
Job "SMILEBD"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:54:38

Note that here only one table is exported.
Because with first INCLUDE parameter,
include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')"

first 3 tables are selected
and with second INCLUDE parameter
include =TABLE:"LIKE 'ACC%' "
both output are ANDed and only one table is selected that is ACCOUNT_GROUP (which starts with word ACC.)

Solution of the Problem
It is recommended not to use multiple INCLUDE or EXCLUDE parameter in data pump jobs and read http://arjudba.blogspot.com/2008/04/datapump-parameters-include-and-exclude.html If you are in above scenario then only add one INCLUDE parameter and add all the tables within IN clause like below,

include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP','CV', 'CV_EXPERIENCE', 'CV_EDUCATION')"

There is no valid reason to use multiple INCLUDE or EXCLUDE parameter in your data pump operation. With only one INCLUDE or EXCLUDE parameter you can do your job.

No comments:

Post a Comment