Home » RDBMS Server » Server Utilities » ORA-31655: no data or metadata objects selected for job (Oracle 10g)
ORA-31655: no data or metadata objects selected for job [message #619841] Fri, 25 July 2014 08:37 Go to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member


Hi All,


create directory tmp_dir  as '/home/oracle';

create user scott_new identified by tiger;

alter user scott_new account unlock;

alter user scott_new quota 100m on users;

grant dba to scott_new with admin option;

expdp scot/tiger tables=emp directory=TEMP_DIR dumpfile=emp.dmp logfile=expemp.log;
 
I am getting the following error while importing the dump file.

impdp scott_new/tiger tables=emp directory=TEMP_DIR dumpfile=emp.dmp logfile=impemp.log ;
ORA-31655: no data or metadata objects selected for job



Regards,
Nathan
Re: ORA-31655: no data or metadata objects selected for job [message #619844 is a reply to message #619841] Fri, 25 July 2014 08:42 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Use REMAP_SCHEMA
Re: ORA-31655: no data or metadata objects selected for job [message #619845 is a reply to message #619841] Fri, 25 July 2014 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
IMPDP HELP=YES

no objects owed by SCOTT_NEW exist in the dump file; so posted error message is appropriate & correct
Re: ORA-31655: no data or metadata objects selected for job [message #619846 is a reply to message #619844] Fri, 25 July 2014 08:44 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thanks john I used but not working.
remap_schema=scott:scott_new
Re: ORA-31655: no data or metadata objects selected for job [message #619847 is a reply to message #619846] Fri, 25 July 2014 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
my car is not working
tell me how to make my car go

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: ORA-31655: no data or metadata objects selected for job [message #619848 is a reply to message #619846] Fri, 25 July 2014 08:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Fri, 25 July 2014 19:14
Thanks john I used but not working.
remap_schema=scott:scott_new


Does scott_new has any objects at all?

BlackSwan wrote on Fri, 25 July 2014 19:13
IMPDP HELP=YES

no objects owed by SCOTT_NEW exist in the dump file; so posted error message is appropriate & correct

Re: ORA-31655: no data or metadata objects selected for job [message #619849 is a reply to message #619848] Fri, 25 July 2014 08:51 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

I guess that objects will be created after importing of dump file.

[Updated on: Fri, 25 July 2014 08:53]

Report message to a moderator

Re: ORA-31655: no data or metadata objects selected for job [message #619850 is a reply to message #619849] Fri, 25 July 2014 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am thinking that when I will import the dump file the listed object will be created thereafter.

Not everything you think exists in reality.

if you log into the DB as SCOTT_NEW & did SELECT COUNT(*) FROM EMP; what would result?
Re: ORA-31655: no data or metadata objects selected for job [message #619852 is a reply to message #619849] Fri, 25 July 2014 08:57 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
sss111ind wrote on Fri, 25 July 2014 19:21
I guess that objects will be created after importing of dump file.


Why guess? Does Oracle guess while throwing an error? Check it yourself and be sure.
Re: ORA-31655: no data or metadata objects selected for job [message #619853 is a reply to message #619852] Fri, 25 July 2014 09:05 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

I understood like impdp will get the schema objects(scott) from the dump file created by the expdp and recreate those objects in a new schema(scott_new). But impdp itself is failing giving below error.
ORA-31655: no data or metadata objects selected for job


And if I put select * from tab; then also I am getting no rows selected

[Updated on: Fri, 25 July 2014 09:07]

Report message to a moderator

Re: ORA-31655: no data or metadata objects selected for job [message #619855 is a reply to message #619853] Fri, 25 July 2014 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You need to be smarter than the tools you (ab)use.

>And if I put select * from tab; then also I am getting no rows selected
once again Oracle accurately reports reality.

Do NOT tell us what you think you do or see SHOW us using COPY & PASTE!

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/
Re: ORA-31655: no data or metadata objects selected for job [message #619920 is a reply to message #619855] Sat, 26 July 2014 02:20 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

I have a scott schema available but I want to have only table objects to be moved to scott_new. How can I achieve that.

[Updated on: Sat, 26 July 2014 02:21]

Report message to a moderator

Re: ORA-31655: no data or metadata objects selected for job [message #619923 is a reply to message #619920] Sat, 26 July 2014 02:48 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can this topic finally be killed?
c:\users\john>
c:\users\john>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 26 08:44:02 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

orclz> grant dba to scott_new identified by tiger;

Grant succeeded.

orclz> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

c:\users\john>expdp system/oracle file=emp.dmp tables=scott.emp

Export: Release 12.1.0.1.0 - Production on Sat Jul 26 08:44:41 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=emp.dmp" Location: Command Line, Replaced with: "dumpfile=emp.dmp"
Legacy Mode has set reuse_dumpfiles=true parameter.
Legacy Mode has set nologfile=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** dumpfile=emp.dmp tables=scott.emp reuse_dumpfiles=true nologfile=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "SCOTT"."EMP"                                8.75 KB      14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  C:\APP\ORACLE\ADMIN\ORCLZ\DPDUMP\EMP.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jul 26 08:45:11 2014 elapsed 0 00:00:27


c:\users\john>impdp system/oracle file=emp.dmp full=yes remap_schema=scott:scott_new

Import: Release 12.1.0.1.0 - Production on Sat Jul 26 08:46:10 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=emp.dmp" Location: Command Line, Replaced with: "dumpfile=emp.dmp"
Legacy Mode has set nologfile=true parameter.
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=emp.dmp full=yes remap_schema=scott:scott_new nologfile=true
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT_NEW"."EMP"                            8.75 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"SCOTT_NEW"."FK_DEPTNO" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "SCOTT_NEW"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "SCOTT_NEW"."DEPT" ("DEPTNO") ENABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Jul 26 08:46:27 2014 elapsed 0 00:00:15


c:\users\john>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 26 08:46:48 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

orclz> select count(*) from scott_new.emp;

  COUNT(*)
----------
        14

orclz>
Re: ORA-31655: no data or metadata objects selected for job [message #619926 is a reply to message #619923] Sat, 26 July 2014 03:27 Go to previous messageGo to next message
sss111ind
Messages: 634
Registered: April 2012
Location: India
Senior Member

Thank you John for you explanation. For me,after removing tables=emp from the above code the impdp has started working.

 impdp scott_new/tiger  directory=TEMP_DIR dumpfile=emp.dmp logfile=impemp.log remap_schema=scott:scott_new
 

[Updated on: Sat, 26 July 2014 03:31]

Report message to a moderator

Re: ORA-31655: no data or metadata objects selected for job [message #619928 is a reply to message #619926] Sat, 26 July 2014 04:17 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So there is no EMP table in your export file or you exported only the data and the target schema has not this table.

Previous Topic: Sql Loader
Next Topic: Consistent backup using data pump
Goto Forum:
  


Current Time: Fri Mar 29 00:43:07 CDT 2024