expdp dynamic list of tables

If you want to export a list of tables where the list can be derived from a query then this is how you do it - it can save having to type in lots of table names.

And here i'm doing it without a parfile which makes it pretty painful to get the syntax right....

expdp / schemas=ALIGNE include=TABLE:\"IN \(select table_name from dba_tables where owner=\'ALIGNE\' and table_name like \'A\%\'\)\"


This will extract all tables in the ALIGNE schema that start with an A - shows the theory and it can be any logic you like....

The size estimate is all wrong but it shows the scripe working

14 comments:

  1. What shall I do if I want an additional table in the list. The following script does NOT work for me.

    expdp / schemas=ALIGNE include=TABLE:\"IN \(select table_name from dba_tables where owner=\'ALIGNE\' and table_name like \'A\%\ union 'TB_addTable' '\)\"

    ReplyDelete
  2. Hi,
    You need to make a small tweak to say
    union select 'TB_addTable' from dual

    rather than just union tablename

    I had trouble getting this to work in 11.2.0.3.0 for some reason it just wouldn't accept it - but it seemed fine in 11.2.0.3.1 (the other version db i had on this server) - so there may be a little bug around this.

    The other alternative is just to create a view in the db - then you can just change the include to select table_name from view.

    And view can be any SQL statement you like.

    Cheers,
    Rich

    ReplyDelete
  3. Update from me - it does work fine in the base version of 11.2.0.3 - my earlier messing around with datapump internals was stopping it working.....

    ReplyDelete
  4. Thank you so much! You save a lot of my time.
    -Anil

    ReplyDelete
  5. Hey i want to make dump on 10g (only metadata from a list of table)

    i create a table test_table which contains all table_names that i need.

    i try expdp

    EXPDP "'test/test@test10 as sysdba'" schemas=test_unicode directory=test_unicode dumpfile=test_unicode.dmp logfile=test_unicode.log Content=metadata_only include=TABLE:\"IN \(select table_name from test_tables\)\"

    Can You please help me.

    IF it es possible or exists an other way to get metadata from list of tables. Are there some mistakes in the Statement?





    ReplyDelete
  6. Hi,
    Are you actually getting errors when you run that - what do you actually need - that command if syntax is correct will create a binary file on hte database server that only import can read - do you want a 'text' file containing all the create table commands?

    If that is the case you should maybe take a look at the sqlfile option which will put all the commands into a text file.

    Cheers,
    Rich

    ReplyDelete
  7. Hi,

    i want to get a test.dmp file which contains only the metadata of e.g. 50 tables. In the next step i will delete Constraints and Indexes from this tables. Then do some Work e.g. Alter columns -> change Datatype to UNICODE e.g.varchar2 to nvarchar2. When work is done, i want to use
    IMPDP INCLUDE=CONSTRAINT,INDEX to re-add from test.dmp.

    Thanks,
    Paul

    ReplyDelete
  8. OK - that process should work - are you getting an error then when you try and run the expdp command?

    Cheers,
    Rich

    ReplyDelete
  9. Hi Harrision, I have select statement with table_name like 'ABC%DE'. When i ran it on SQL , it gave me 204 list of tables. i want to include this pattern in expdp command but it is givinb me errorORA-39071: Value for INCLUDE is badly formed; ORA-00907: missing right parenthesis.

    This is how i used include clause in expdp.
    INCLUDE=TABLE:\"IN\(select table_name from dba_tables where owner=\'XYZ\' and table_name like \'ABC%DE%\'\)\"

    Is anything wrong in pattern matching?

    select statment ran successfully on SQL Promot , generating 204 rows with table. Here is select output:

    ------------
    ABC1050_1_DE
    ABC1049_1_DE
    ABC1046_1_DE
    ABC1045_1_DE
    ABC1043_1_DE
    ABC1039_1_DE
    204 rows selected.

    ReplyDelete
  10. correction in previous reply

    This is how i used include clause in expdp.
    INCLUDE=TABLE:\"IN\(select table_name from dba_tables where owner=\'XYZ\' and table_name like \'ABC%DE\'\)\"

    ReplyDelete
    Replies
    1. Hi,
      At a guess without checking it seems you probably need to escape the % character in your command - so something like

      INCLUDE=TABLE:\"IN\(select table_name from dba_tables where owner=\'XYZ\' and table_name like \'ABC\%DE\'\)\"

      Delete
  11. Thanks Much for quick reply. I tried with escaping % . The error is same .

    INCLUDE=TABLE:\"IN\(select table_name from dba_tables where owner=\'XYZ\' and table_name like \'ABC\%DE\'\)\"

    ORA-39001: invalid argument value
    ORA-39071: Value for INCLUDE is badly formed.
    ORA-00907: missing right parenthesis

    ReplyDelete
  12. To simply these complex (ABC%DE) tablenames , i tried to extract all tables in the XYZ schema that start with an ABC (same way as in your example.)

    Here is the INCLUDE Clause:
    INCLUDE=TABLE:\"IN\(select table_name from dba_tables where owner=\'XYZ\' and table_name like \'ABC\%\'\)\"

    I am getting same error.
    ORA-39001: invalid argument value
    ORA-39071: Value for INCLUDE is badly formed.
    ORA-00907: missing right parenthesis

    Any help much appreciate.

    ReplyDelete
  13. Sorry been too busy to reply - easiest way to deal with this is to use a parfile with the query in - then you don't need all the escape characters and its much easier

    ReplyDelete