Database Software

impdp system/pwd dull=y directory=YOUR_DUMP_DIR exclude=SCHEMA:"='schema1,schema2'"

I tried and I got error

LRM-00116: syntax error at 'SCHEMA:' following '='

$ cat /importarfullexclude.sh
impdp system/????@?????? exclude=SCHEMA:"='DBSNMP','MGMT_VIEW','SYSMAN','SYS','SYSTEM','FLOWS_FILES','MDSYS','SCOTT','WMSYS','APPQOSSYS','XS$NULL','APEX_030200','OWBSYS_AUDIT','MDDATA','OWBSYS','ORACLE_OCM','ORDDATA','ANONYMOUS','OUTLN','DIP','EXFSYS','APEX_PUBLIC_USER','XDB','ORDSYS','SPATIAL_CSW_ADMIN_USR','CTXSYS','SPATIAL_WFS_ADMIN_USR','ORDPLUGINS','OLAPSYS','SI_INFORMTN_SCHEMA'" DIRECTORY=dirorigen LOGFILE=DATA_PUMP_DIR:imp_full_.log FULL=y DUMPFILE=fullbk.dmp
$

I am just... seeing the error with the '

Ignore this reply

Edited by: user2931261 on Feb 18, 2011 11:21 AM

what is the error you are getting?

I tried as you recommed but I got an error as well

impdp system/???@????? exclude=SCHEMA:"='DBSNMP,MGMT_VIEW,SYSMAN,SYS,SYSTEM,FLOWS_FILES,MDSYS,SCOTT,WMSYS,APPQOSSYS,XS$NULL,APEX_030200,OWBSYS_AUDIT,MDDATA,OWBSYS,ORACLE_OCM,ORDDATA,ANONYMOUS,OUTLN,DIP,EXFSYS,APEX_PUBLIC_USER,XDB,ORDSYS,SPATIAL_CSW_ADMIN_USR,CTXSYS,SPATIAL_WFS_ADMIN_USR,ORDPLUGINS,OLAPSYS,SI_INFORMTN_SCHEMA'" DIRECTORY=dirorigen LOGFILE=DATA_PUMP_DIR:imp.log FULL=y DUMPFILE=fullbk.dmp
$
$ ./importarfullexclude.sh
LRM-00116: syntax error at 'SCHEMA:' following '='

$



I tried on this way and I got an error again...

;;;
Import: Release 11.2.0.2.0 - Production on Fri Feb 18 11:35:00 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00920: invalid relational operator
$


impdp system/???@????? exclude=SCHEMA:"'DBSNMP,MGMT_VIEW,SYSMAN,SYS,SYSTEM,FLOWS_FILES,MDSYS,SCOTT,WMSYS,APPQOSSYS,XS$NULL,APEX_030200,OWBSYS_AUDIT,MDDATA,OWBSYS,ORACLE_OCM,ORDDATA,ANONYMOUS,OUTLN,DIP,EXFSYS,APEX_PUBLIC_USER,XDB,ORDSYS,SPATIAL_CSW_ADMIN_USR,CTXSYS,SPATIAL_WFS_ADMIN_USR,ORDPLUGINS,OLAPSYS,SI_INFORMTN_SCHEMA'" DIRECTORY=dirorigen LOGFILE=DATA_PUMP_DIR:imp_2011.log FULL=y DUMPFILE=fullbk_2011.dmp
$

  
  exclude=SCHEMA:"IN('DBSNMP','..',....)"  

depending on your OS you may have to use escape characters for the ". What OS you are using?

You may also try

{code} impdp "exclude=schema:\"='schema1'\"" {code}

Edited by: pransuj on Feb 18, 2011 9:52 AM

Ok I am going to try in that way

use of Parameter File precludes shell processing from complicating the needed syntax

impdp username/password parfile=is_easy.ctl

Thanks sb92075

Right now , I am using parfile because the quotation marks generates troubles in the command line.

I found this note

•Parameter File Interface: Enables you to specify command-line parameters in a parameter file. The only exception is the PARFILE parameter because parameter files cannot be nested. The use of parameter files is recommended if you are using parameters whose values require quotation marks. See Use of Quotation Marks On the Data Pump Command Line.

Yes. Using parameter file will be lot easier. If not then you use correct escake characters.

If parameter file is not used then in unix special care needs to be taken in syntax of expdp and impdp, in particular all the single quotes and double quotes needs to be preceded with the special character ‘\’ .The syntax for windows and unix

Windows:

D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN (’EMP’, ‘DEP’)\”

Unix:

% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\” 

[http://www.oraclefaq.net/2007/03/09/expdp-datapump-excludeinclude-parameters/]

.

Edited by: user2931261 on Feb 18, 2011 12:17 PM

I resolved the trouble with a parfile so:

$ cat myparfile1.par
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='SYSTEM'"
exclude=SCHEMA:"='SYS'"
exclude=SCHEMA:"='MGMT_VIEW'"
exclude=SCHEMA:"='SI_INFORMTN_SCHEMA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDPLUGINS'"
exclude=SCHEMA:"='CTXSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='XDB'"
exclude=SCHEMA:"='EXFSYS'"
exclude=SCHEMA:"='ANONYMOUS'"
exclude=SCHEMA:"='ORDDATA'"
exclude=SCHEMA:"='OWBSYS'"
exclude=SCHEMA:"='OWBSYS_AUDIT'"
exclude=SCHEMA:"='APEX_030200'"
exclude=SCHEMA:"='APPQOSSYS'"
exclude=SCHEMA:"='WMSYS'"
exclude=SCHEMA:"='MDSYS'"
exclude=SCHEMA:"='FLOWS_FILES'"
exclude=SCHEMA:"='SYSMAN'"
exclude=SCHEMA:"='DBSNMP'"
exclude=SCHEMA:"='SPATIAL_WFS_ADMIN_USR'"
exclude=SCHEMA:"='SPATIAL_CSW_ADMIN_USR'"
exclude=SCHEMA:"='APEX_PUBLIC_USER'"
exclude=SCHEMA:"='DIP'"
exclude=SCHEMA:"='ORACLE_OCM'"
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='XS$NULL'"
exclude=SCHEMA:"='SCOTT'"
$

Thanks for the colaboration on this item.