Home » RDBMS Server » Server Utilities » Question on IMPDP working with wildcards (Oracle 10.2.0.3 HP/UX Itanium 11.31)
Question on IMPDP working with wildcards [message #395767] Thu, 02 April 2009 09:40 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Hi all,

I am working quite often with expdp and impdp. But now I have to restrict the import of a full export file.

We have several tables we do need. To work with a function we have used with imp is not possible. To place all table-Names in TABLES does not work (restriction in 10.2 to 4000 byte - list is longer)

I tried
 INCLUDE=TABLE:"LIKE '??_%' " 

 INCLUDE=TABLE:"LIKE '??1%' " 


The tables we need has 2 character before the UNDERLINE or the 1 and some chars behind.

It does not generates an error, but it seems to find tables named ??_% and ??1% without using ? (Questionmark) as a single wildcard.

Is there a possibility to mask the wildcards in IMPDP or EXPDP?
otherwise we do need to set up different impdp's with the TABLES option.

regards
Uwe
Re: Question on IMPDP working with wildcards [message #395779 is a reply to message #395767] Thu, 02 April 2009 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Is there a possibility to mask the wildcards in IMPDP or EXPDP?
No.

>otherwise we do need to set up different impdp's with the TABLES
option.
yes

Re: Question on IMPDP working with wildcards [message #395781 is a reply to message #395779] Thu, 02 April 2009 10:06 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Thanks Black Swan

I am not happy to read this, but I have had thought that this will the way to use

Re: Question on IMPDP working with wildcards [message #396420 is a reply to message #395767] Mon, 06 April 2009 09:39 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Just one more problem

I am now working with the solution to store the table_names in a new table and try to set the table_names with INCLUDE

USERID = xxxx/xxx@xxxx
DUMPFILE = expdp_010409.dmp
LOGFILE = impdp_060409.log
DIRECTORY = data_pump_dir
CONTENT = DATA_ONLY
PARALLEL = 4
INCLUDE = TABLE:"IN (SELECT table_name FROM DP_TABLES)"


My Problem now is, that the import only takes seconds for 2600 tables with 16 GB of data.
I know that impdp is fast, but this is too fast.
I get NO Error - it finished successful, but does not insert data.
How do I need to format the INCLUDE Parameter to get the table_data

The table DP_TABLES look simple:
create table DP_TABLES (table_name varchar2(50));
insert into DP_TABLES
select table_name from user_tables
where (substr(table_name,3,1) = '_'
or substr(table_name,3,1) = '1');


and was filled with over 2600 table_names.

any help is welcome
kind regards
uwe

Re: Question on IMPDP working with wildcards [message #396518 is a reply to message #395767] Tue, 07 April 2009 01:42 Go to previous messageGo to next message
Uwe
Messages: 260
Registered: February 2003
Location: Zürich, Switzerland
Senior Member
Sorry,

it was my fault. I just found the cause in my scripts.
Shocked I just truncated all user tables with a _ on third place. Also dp_tables was truncated. I just changed the table_name to dptables and it works

regards
Uwe
Cool Laughing
Re: Question on IMPDP working with wildcards [message #396653 is a reply to message #396518] Tue, 07 April 2009 08:13 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Thanks for your feedback.

Babu
Previous Topic: Export via conventional path v/s direct path
Next Topic: IMPDP with DB_LINK and different OS
Goto Forum:
  


Current Time: Sat Dec 03 22:39:57 CST 2016

Total time taken to generate the page: 0.24421 seconds