1

Suppose I want to export

  • Views named V1, V2
  • Functions named f1, f2
  • Procedures named p1, p2

  • Tables named T1, T2

  • username : system

  • password : manager sid /
  • schema : DB3

So I fire command on CMD as follow,

(1) expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=view:"IN ('V1','V2')", function:"IN ('f1','f2')", procedure:"IN ('p1','p2')", table:"IN ('T1','T2')"

(2) expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=view:"IN ('V1','V2')" include=function:"IN ('f1','f2')" include=procedure:"IN ('p1','p2')" include=table:"IN ('T1','T2')"

These both commands are not working:

Error : ORA-39071: Value for INCLUDE is badly formed.

Following command works but it exports specified Tables only. It doesn't export views, procedures and functions.

(3) expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=view,procedure,function,table:"IN ('T1','T2')"

What to do?

APC
  • 144,005
  • 19
  • 170
  • 281
अक्षय परूळेकर
  • 314
  • 4
  • 16

1 Answers1

3

ORA-39071: Value for INCLUDE is badly formed

The command line doesn't like the quote marks. There are two ways to handle this.

The first is to escape the quotes: 

expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log 
include=view:\"IN (\'V1\',\'V2\')\" include=function:\"IN (\'f1\',\'f2\')\" include=procedure:\"IN (\'p1\',\'p2\')\" include=table:\"IN (\'T1\',\'T2\')\"

If that seems tedious (and it is) the second option would be to define a parameter file. You can include all your options in that, without escaping the quotes, and just run expdp with the PARFILE parameter.

There are no any changes in Schema 'DBpractice' in which I want to import the tables, views, functions and procedures that have been exported from schema named DB3.

That's really a new question, but the solution is straightforward enough: you need to run impdp with the REMAP_SCHEMA parameter which takes source_schema:target_schema. So in your case that would be

impdp system/manager directory=backupdir dumpfile=backup.dmp remap_schema=db3:DBpractice
APC
  • 144,005
  • 19
  • 170
  • 281
  • Thanks. It worked after I created and granted permissions to directory. Now I want to export it to Oracle 10g. C:\Users\Dell 7559>expdp system/manager@DB3 directory=backupdir dumpfile=backup.dmp logfile=backup.log include=table:\"in (\'STOCK\')\" version=10.2 ******** All tables are being exported from Oracle-12c to Oracle-10g except one having definition as follow --> create table stock(ModID varchar(20) primary key, Name varchar(30), Type varchar(15) ,mQty number, cmpID number, price number, Warranty number); ********* – अक्षय परूळेकर Nov 10 '18 at 06:06