12 Replies Latest reply: Sep 20, 2013 9:40 AM by user9538263 RSS

    impdp using EXCLUDE

    834073
      Hi

      I want to import a full database excluding a list of schemas. I know I can use the parameter EXCLUDE to do that but I need to exclude several schemas and I am having problem writing the appropiate list in the parameter EXCLUDE. Can you help me?

      Thanks
        • 1. Re: impdp using EXCLUDE
          DBA_1976
          impdp system/pwd dull=y directory=YOUR_DUMP_DIR exclude=SCHEMA:"='schema1,schema2'"
          • 2. Re: impdp using EXCLUDE
            834073
            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
            • 3. Re: impdp using EXCLUDE
              DBA_1976
              what is the error you are getting?
              • 4. Re: impdp using EXCLUDE
                834073
                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
                $
                • 5. Re: impdp using EXCLUDE
                  DBA_1976
                    
                    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
                  • 6. Re: impdp using EXCLUDE
                    834073
                    Ok I am going to try in that way
                    • 7. Re: impdp using EXCLUDE
                      sb92075
                      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
                      • 8. Re: impdp using EXCLUDE
                        834073
                        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.
                        • 9. Re: impdp using EXCLUDE
                          DBA_1976
                          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/]
                          • 10. Re: impdp using EXCLUDE
                            834073
                            .

                            Edited by: user2931261 on Feb 18, 2011 12:17 PM
                            • 11. Re: impdp using EXCLUDE
                              834073
                              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.
                              • 12. Re: impdp using EXCLUDE
                                user9538263

                                This was useful for me - full import excluding any schema already present in a database:

                                impdp "'/ as sysdba'" full=y 'exclude=SCHEMA:"IN(select username from all_users)"'

                                I've used it to migrate database from 10.2 to freshly installed 11.2 database. This way it will ignore all system schemas like "DBSNMP", "DIP", "SYS", "SYSTEM" etc but recreate all users, tablespaces and schemas from old database.