oracle-databaseexportimpdpexpdp

How to export selected functions, selected procedures, selected views and selected tables only from oracle database using command line?


Suppose I want to export

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?


Solution

  • 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