6 Replies Latest reply: Nov 20, 2012 2:06 AM by Dean Gagne-Oracle RSS

    EXP or EXPDP first X rows

    SLOtransmission
      Hay,
      I have searched the net, and didn't get explicit answer to this.
      Is there any option to export one complete schema with all objects in it, but from each table only export first 100 rows for example?

      Thanks in advance!
        • 1. Re: EXP or EXPDP first X rows
          Dean Gagne-Oracle
          You can do this with data pump. Add

          Query=where rownum < 101

          To either your export command or your import command.

          Hope this helps
          Dean
          • 2. Re: EXP or EXPDP first X rows
            SLOtransmission
            Like this:
            expdp schemas=SCOTT directory=data_pump_dir dumpfile=SCOTT.dmp logfile=SCOTT.log query="where rownum < 101"

            I get:
            Export: Release 10.2.0.5.0 - 64bit Production on Torek, 20 November, 2012 8:15:37

            Copyright (c) 2003, 2007, Oracle. All rights reserved.

            Username: / as sysdba

            Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
            With the Partitioning, Real Application Clusters, OLAP, Data Mining Scoring Engine
            and Real Application Testing options
            ORA-39001: invalid argument value
            ORA-39035: Data filter SUBQUERY has already been specified.
            • 3. Re: EXP or EXPDP first X rows
              Dean Gagne-Oracle
              I thunk you need to add single quotes around the double quotes.

              Query=single double where clause double single

              If you do this on the command, you will have to escape the single quotes. You could put it in a parameter file and not worry about the escapes.

              Thanks

              Dean
              • 4. Re: EXP or EXPDP first X rows
                SLOtransmission
                Yes, I already figured it out, but if I use:
                expdp schemas=SCOTT directory=data_pump_dir dumpfile=SCOTT.dmp logfile=SCOTT.log query='"rownum<101"'

                I got errors on some of the tables (on tables that are not empty):
                ORA-31693: Table data object "SCOTT"."S_PRODUCT" failed to load/unload and is being skipped due to error:
                ORA-00933: SQL command not properly ended
                ORA-31693: Table data object "SCOTT"."S_REGION" failed to load/unload and is being skipped due to error:
                ORA-00933: SQL command not properly ended
                ORA-31693: Table data object "SCOTT"."S_TITLE" failed to load/unload and is being skipped due to error:
                ORA-00933: SQL command not properly ended
                ORA-31693: Table data object "SCOTT"."S_WAREHOUSE" failed to load/unload and is being skipped due to error:
                ORA-00933: SQL command not properly ended
                ORA-31693: Table data object "SCOTT"."TABLE_IDSEQ" failed to load/unload and is being skipped due to error:
                ORA-00933: SQL command not properly ended
                . . exported "SCOTT"."BONUS" 0 KB 0 rows
                . . exported "SCOTT"."BOOTS" 0 KB 0 rows
                . . exported "SCOTT"."DEMOKIT_CLASSES" 0 KB 0 rows
                . . exported "SCOTT"."DEMOKIT_DEMOS" 0 KB 0 rows
                . . exported "SCOTT"."DEMOKIT_DEMO_SCRIPTS" 0 KB 0 rows
                . . exported "SCOTT"."DEMOKIT_INSTALLED_SCRIPTS" 0 KB 0 rows
                • 5. Re: EXP or EXPDP first X rows
                  Dean Gagne-Oracle
                  You need the

                  Where

                  In your clause.

                  Single double where xxxxx double single.

                  Dean
                  • 6. Re: EXP or EXPDP first X rows
                    SLOtransmission
                    OMG, how I missed that?
                    Now it woks like a charm :)

                    expdp schemas=SCOTT directory=data_pump_dir dumpfile=SCOTT.dmp logfile=SCOTT.log query='"where rownum < 101"'


                    THANKS A LOT!

                    Edited by: SLO transmission on 20.11.2012 0:19