3 Replies Latest reply: Sep 8, 2013 9:11 PM by afaltyno RSS

    Rows only trying to extract

    afaltyno

      Hi Everyone,

      I spent about 3 hours last night working on a small .par script to do rows only.  At the end of those 3 hours I was totally aggravated with the problems of the tick marks and the correct position they are supposed to be in.  I tried researching the oracle literature to read up on how to use those tick marks but to no avail.  Right now I am totally confused as to how to use the tick marks in the script below and in general.  I rather read about something and learn how to do it for the future than have someone just give me the answer to one problem.  I hope you can understand.  I am just trying to extract the rows only from one table.

      Thanks for your help in advance.

       

      Regards,

       

      Al

       

      I am on a windows 7 laptop.

       

      I checked the query and it in fact returns the rows I need.

       

      I am in as the scott user running the expdp with the following command:

       

      >expdp parfile=rowsonly.par

      >username:   scott

      >password: **********

      Here is what I have:

       

      1. Rowsonly.par

      directory=dpump_dir1

      dumpfile=scottrows.dmp

      logfile=scottrows.log

      include=table:"='LVGSTN_ADD'"

      query=LVGSTN_ADD:'"WHERE L_NAME LIKE 'A%'"' 

       

       

      My output is as follows:

      c:\app\Oracle\parfiles>expdp parfile=rowsonly.par

      Export: Release 11.2.0.1.0 - Production on Sat Sep 7 18:28:19 2013

      Username: scott

      Password:

      Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

      Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      ORA-39001: invalid argument value                               < === I made several attempts to correct this.

      ORA-39071: Value for TABLES is badly formed.             < === I made several attempts to correct this.

      ORA-00920: invalid relational operator                            < === I made several attempts to correct this.

      c:\app\Oracle\parfiles>

        • 1. Re: Rows only trying to extract
          DK2010

          Hi,

           

          You can try this

           

          directory=dpump_dir1

          dumpfile=scottrows.dmp

          logfile=scottrows.log

          include=table:"= 'LVGSTN_ADD'"

          query=LVGSTN_ADD:"WHERE L_NAME LIKE 'A%'"

           

          HTH

          • 2. Re: Rows only trying to extract
            afaltyno

            Evening HTH

             

            I had tried that this evening and it didn't work. It returned zero rows when there should have been over 1700.

             

            I can't believe a process that seems so simple is incredibly hard when there is no accurate documentation explaining this stuff.

             

            Thanks for your help anyway.

             

            Regards,

             

            al

            • 3. Re: Rows only trying to extract
              afaltyno

              Hi DK2010

               

              I found the problem.  I substituted that table for another and it worked.  When I compared both tables I saw that the one I was using was an EXTERNAL TABLE.  Evidently oracle can't operate on that type.  I simply forgot all about that table and its source.

               

              Now I can put my lead pipe away and go to sleep.

               

              regards,

               

              al