This discussion is archived
6 Replies Latest reply: Nov 20, 2012 12:06 AM by Dean Gagne RSS

EXP or EXPDP first X rows

SLOtransmission Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    You need the

    Where

    In your clause.

    Single double where xxxxx double single.

    Dean
  • 6. Re: EXP or EXPDP first X rows
    SLOtransmission Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points