Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    81

    Unanswered: export dump tables with wildcard

    Hi,

    I need to take a schema export on a 10.2 database of only specific tables but
    not sure how? I have over a 100 tables, belonging to one schema with the naming
    convention of ABC_* but how do I use the include table parameter in an
    export dump with a wildcard?

    for example is the syntax similar to this - can someone advise please?

    expdp scott/tiger@db10g include tables=ABC_% dumpfile=schema_tables.dmp logfile=expschema.log

    Many thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I am reasonably confident that wildcard will not work on the command line.
    I have an idea, which is untested; but may work & be reasonably painless.

    CREATE USER ABC_EXPORT

    Manually GRANT SELECT ON every ABC* table to ABC_EXPORT
    then do schema export datapump.
    It will throw errors on every object but ABC_* tables
    You'll need to be responsible for PK/FK, Triggers, & all other object types not included above.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2010
    Posts
    81
    Hi,

    I tried that but that did not work.

    with the standard export (exp) this was quite straightforward
    but with expdp I am not so sure. It has something to do with
    include=table: but like I said I am not entirely sure

    thanks in advance

  4. #4
    Join Date
    Feb 2005
    Posts
    57
    Data Pump Export gives the following:

    Code:
    INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"
    INCLUDE=INDEX:"LIKE 'EMP%'"
    It uses wildcard for Index so I assume it could be used for Table also!

  5. #5
    Join Date
    Jun 2010
    Posts
    81

    Export dump schema tables using wildcard

    this is the error message I recieve


    expdp xdw include=table:"LIKE 'XDW_%'" dumpfile=xdw_expdp.dmp logfile=xdw_expdp.log

    Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 09 September, 2010 8:10:54

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

    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    ORA-39001: invalid argument value
    ORA-39071: Value for INCLUDE is badly formed.
    ORA-00936: missing expression

  6. #6
    Join Date
    Jun 2010
    Posts
    81

    expdp schema table with wildcard - now works

    I have managed to get the expdp with wildcard to work

    this is the syntax for anyone else having this issue:-

    expdp xdw include=table:"LIKE'%XDW_%'" dumpfile=xdw_expdp.dmp logfile=xdw_expdp.log

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •