1 2 Previous Next 18 Replies Latest reply: Mar 25, 2013 3:28 PM by 864103 RSS

    exp and imp

    864103
      Hi guys ,

      i need to exp table but with condition and imp back to another database ,

      we have 2 database and there is no link between tow server and i need to import the tables but with condition and imported again
      to oracle 11g
       exp abs/abs tables=texp query=abs.texp:"where hiredate > '01-JAN-13'"
      LRM-00112: multiple values not allowed for parameter 'query'
      
      EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
      EXP-00000: Export terminated unsuccessfully
      
      
      
      
       exp abs/abs tables=texp query="where hiredate > '1-JAN-13'"
      LRM-00112: multiple values not allowed for parameter 'query'
      
      EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
      EXP-00000: Export terminated unsuccessfully
        • 1. Re: exp and imp
          Kh$n
          Hi try below statments

          exp abs/abs tables=texp query='where "hiredate" > ''1-JAN-13'''
          • 2. Re: exp and imp
            Nicolas.Gasparotto
            You may try to use a parameter file, most often it is better to bypass the quotes OS interpretation. by the way, you're on 11g, why not using expdp/impdp ?

            Nicolas.
            • 3. Re: exp and imp
              Salman Qureshi
              Hi,
              Try following
               exp abs/abs tables=texp query='where hiredate > ''1-JAN-13'' '
              Date value is not enclosed within double quotes, but it is enclosed in 2 single quotes on both sides of the date string

              Salman
              • 4. Re: exp and imp
                864103
                please check the below still error
                [oracle@TCCBS ~]$ exp abs/abs tables=texp query='where "hiredate" > ''1-JAN-13'''
                LRM-00112: multiple values not allowed for parameter 'query'
                
                EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
                EXP-00000: Export terminated unsuccessfully
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$ exp abs/abs tables=texp query='where "hiredate" > '1-JAN-13''
                LRM-00112: multiple values not allowed for parameter 'query'
                
                EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
                EXP-00000: Export terminated unsuccessfully
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$ exp abs/abs tables=texp query='where "hiredate" > "1-JAN-13"'
                LRM-00112: multiple values not allowed for parameter 'query'
                
                EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
                EXP-00000: Export terminated unsuccessfully
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$
                [oracle@TCCBS ~]$
                • 5. Re: exp and imp
                  Nicolas.Gasparotto
                  You did not provided your OS, but here you go for Unix:
                  oracle@myserver:/home/oracle(o10)# exp myuser@mydb tables=mytable query=mytable:" where mycol='MYVALUE'" file=mydata.dmp
                  LRM-00112: multiple values not allowed for parameter 'query'
                  
                  EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
                  EXP-00000: Export terminated unsuccessfully
                  
                  oracle@myserver:/home/oracle(o10)# exp myuser@mydb tables=mytable query=\" where mycol='MYVALUE'\" file=mydata.dmp
                  
                  Export: Release 10.2.0.4.0 - Production on Mon Mar 25 11:25:22 2013
                  
                  Copyright (c) 1982, 2007, Oracle.  All rights reserved.
                  
                  Password:
                  <snipped>
                  As I told you earlier, you could probably use a parameter file which make it simpler (no need these back-slashes).

                  Nicolas.
                  • 6. Re: exp and imp
                    javeedkaleem dba
                    Hi

                    For export using query you must user "\"

                    for example:

                    exp vas/passwd tables=WAAGE query=\'where GEWICHT \< 9000\'

                    exp vas/passwd tables=ZUBEHOER query=\'where CHANGE_DATE \< '10-APR-12'\'

                    ===============================================================



                    yamvassrv04[oracle]_vas> exp vas/xxx tables=WAAGE query=\'where GEWICHT \< 9000\'

                    Export: Release 11.2.0.3.0 - Production on Mon Mar 25 14:20:20 2013

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


                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
                    server uses AL32UTF8 character set (possible charset conversion)

                    About to export specified tables via Conventional Path ...
                    . . exporting table WAAGE 3 rows exported
                    EXP-00091: Exporting questionable statistics.
                    Export terminated successfully with warnings.
                    yamvassrv04[oracle]_vas>





                    yamvassrv04[oracle]_vas> exp vas/xxxx tables=ZUBEHOER query=\'where CHANGE_DATE \< '10-APR-12'\'

                    Export: Release 11.2.0.3.0 - Production on Mon Mar 25 14:26:25 2013

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


                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Export done in US7ASCII character set and AL16UTF16 NCHAR character set
                    server uses AL32UTF8 character set (possible charset conversion)

                    About to export specified tables via Conventional Path ...
                    . . exporting table ZUBEHOER
                    EXP-00056: ORACLE error 904 encountered
                    ORA-00904: "APR": invalid identifier
                    Export terminated successfully with warnings.
                    yamvassrv04[oracle]_vas>


                    Best Regards
                    Kaleem.
                    • 7. Re: exp and imp
                      864103
                      no it's same error
                      oracle@TCCBS ~]$ exp abs/abs tables=texp query=\'where hiredate \< "1-JAN-13"'\
                      '
                      LRM-00111: no closing quote for value 'where hire'
                      
                      EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
                      EXP-00000: Export terminated unsuccessfully
                      [oracle@TCCBS ~]$
                      [oracle@TCCBS ~]$
                      thanks
                      • 8. Re: exp and imp
                        Mohamed ELAzab
                        Hello,

                        You should use parameter file.Another question i can see you are using 11g.Why dont you use data pump?.
                        Data Pump is faster and have more feqtures and enhancement than regular imp and exp.

                        You can do the following:
                        sqlplus / as sysdba
                        
                        Create directory DPUMP_DIR3  for 'Type here your os path that you want to export to';
                        then touch a file:
                        touch par.txt

                        In this file type the following the following :
                        tables=schema.table_name
                        dumpfile=yourdump.dmp
                        DIRECTORY=DPUMP_DIR3 
                        logfile=Your_logfile.log
                        QUERY =abs.texp:"where hiredate>'01-JAN-13' "
                        then do the following
                        expdp username/password parfile='par.txt'

                        If you will import from Oracle 11g to version 10g then you have to addthe parameter "version=10" to the parameter file above

                        BR
                        Mohamed ELAzab
                        http://mohamedelazab.blogspot.com/
                        • 9. Re: exp and imp
                          864103
                          yes i use it and it work , thanks you
                          exp abs/abs parfile=param.txt file=texp.dmp log=texp.log
                          
                          
                          
                          [oracle@TCCBS ~]$ vim param.txt
                          tables=texp
                          query="where hiredate>'01-JAN-13'"
                          can you please share with me the import command , but the database that imported is under windows and is lower than 11

                          source database is less than 11g under windows
                          target database is 11g asm rac under OL5.6

                          the diff between the database and maybe the characterset is effected???
                          • 10. Re: exp and imp
                            Mohamed ELAzab
                            Hello 861100 ,

                            If it is 10g and you are moving to 11g then dont use the version.in your case you are using imp and exp commands then it should work fine.
                            The characterset of the source database is the same as the destination?.If yes then no worries.If not you have to make sure that the character set that you are moving to is a superset of the source one to avoid data corruption.
                            i advice you to use the Data pump as it is better than the regular exp and imp.

                            BR
                            Mohamed ElAzab
                            http://mohamedelazab.blogspot.com/
                            • 11. Re: exp and imp
                              864103
                              thanks man , so if the source (10G) is less then destination there is no problem .
                              • 12. Re: exp and imp
                                Mohamed ELAzab
                                Hello 861100 ,

                                In the data pump it is working fine.i am not pretty sure about the imp and exp.Try it and let me know if you have an issue.As i can remember there was an issue between 8i and 9i and 10g as you have to use the same export client to import the data.i advice you to try it and see.if it didnot work then use expdp and impdp as i tested them and pretty sure they are working fine between 10g and 11g.

                                Kind Regards
                                Mohamed ELAzab
                                http://mohamedelazab.blogspot.com/
                                • 13. Re: exp and imp
                                  864103
                                  thanks i will check and let you know the result .
                                  • 14. Re: exp and imp
                                    Nicolas.Gasparotto
                                    Original export/import utility : 1) always use the exp version of the lowest version between the two databases, 2) always use the imp version of the target.
                                    Export and import dump (10g and above) : you may follow the same rule as above, or use VERSION parameter during the exportdump.
                                    Following those rules, there were never issue.

                                    Nicolas.
                                    1 2 Previous Next