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
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....
What shall I do if I want an additional table in the list. The following script does NOT work for me.
ReplyDeleteexpdp / schemas=ALIGNE include=TABLE:\"IN \(select table_name from dba_tables where owner=\'ALIGNE\' and table_name like \'A\%\ union 'TB_addTable' '\)\"
Hi,
ReplyDeleteYou 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
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.....
ReplyDeleteThank you so much! You save a lot of my time.
ReplyDelete-Anil
Hey i want to make dump on 10g (only metadata from a list of table)
ReplyDeletei 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?
Hi,
ReplyDeleteAre 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
Hi,
ReplyDeletei 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
OK - that process should work - are you getting an error then when you try and run the expdp command?
ReplyDeleteCheers,
Rich
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.
ReplyDeleteThis 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.
correction in previous reply
ReplyDeleteThis 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\'\)\"
Hi,
DeleteAt 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\'\)\"
Thanks Much for quick reply. I tried with escaping % . The error is same .
ReplyDeleteINCLUDE=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
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.)
ReplyDeleteHere 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.
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
ReplyDeleteHi Harrision,
ReplyDeleteCan 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
adding to the above update when I run it without the order by clause everything works fine:
ReplyDeleteexpdp \"/ 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
HI,
ReplyDeleteNot 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
Hello Harrison,
DeleteThanks 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?
this is how I want the impdp include to look like:
Deleteinclude=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
Only ways it can be done are with this stats trick here
ReplyDeletehttp://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