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

20 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
  14. Hi Harrision,

    Can there exist a "order by" clause in the INCLUDE? I am getting following error :

    expdp \"/ as sysdba\" schemas=SYSTEM logfile=test1.log content=data_only ESTIMATE_ONLY=YES include=TABLE:\"IN \( select table_name from dba_tables where owner=\'SYSTEM\' order by table_name asc\)\"

    Export: Release 12.2.0.1.0 - Production on Wed Mar 14 17:26:54 2018

    Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    ORA-39001: invalid argument value
    ORA-39071: Value for INCLUDE is badly formed.
    ORA-00907: missing right parenthesis

    ReplyDelete
  15. adding to the above update when I run it without the order by clause everything works fine:

    expdp \"/ as sysdba\" schemas=SYSTEM logfile=test1.log content=data_only ESTIMATE_ONLY=YES include=TABLE:\"IN \( select table_name from dba_tables where owner=\'SYSTEM\' \)\"

    Export: Release 12.2.0.1.0 - Production on Wed Mar 14 17:30:26 2018

    Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

    Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
    Starting "SYS"."SYS_EXPORT_SCHEMA_02": "/******** AS SYSDBA" schemas=SYSTEM logfile=test1.log content=data_only ESTIMATE_ONLY=YES include=TABLE:"IN ( select table_name from dba_tables where owner='SYSTEM' )"
    Estimate in progress using BLOCKS method...
    Total estimation using BLOCKS method: 0 KB
    Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at Wed Mar 14 17:30:34 2018 elapsed 0 00:00:07

    ReplyDelete
  16. HI,
    Not sure why you get that error as the SQL looks OK - but actually the order by wont change anything - the export order is defined by some internal code - basically it exports out biggest table first and you cannot influence this.

    Cheers,
    Rich

    ReplyDelete
    Replies
    1. Hello Harrison,

      Thanks a lot for the fast response. The script I provided above is just an example..in fact what I want to do is use an import with an include containing a select with an " order by " clause since I want the tables to be imported in a specific order. Is there no way to add the " Order by" to the INCLUDE?

      Every time I get the same error that the INCLUDE is badly formed. Could this be a bug?

      Delete
    2. this is how I want the impdp include to look like:

      include=table:\"in \( select table_name from system.ANDREI where \mod\(p,4\)=0 order by p asc \)\"

      If I remove the "order by p asc" it works fine

      Delete
  17. Only ways it can be done are with this stats trick here

    http://dbaharrison.blogspot.de/2014/07/getting-datapump-to-follow-orders.html

    or somehow finding out where the order by is applied in the datapump internal code and trying to change it - but realistically that code is going to be wrapped and not possible to change....

    Cheers,
    Rich

    ReplyDelete