Home » RDBMS Server » Server Utilities » data_pump name_expr (10)
data_pump name_expr [message #465493] Wed, 14 July 2010 06:25 Go to next message
sanju_chenna
Messages: 5
Registered: June 2010
Junior Member
HI,
I have name_expr in datapump import procedure,which has long list of tables.
I am storing those names in global temp table and trying to get values in name_expr as follow

DBMS_DATAPUMP.metadata_filter(handle => h1,NAME => 'NAME_EXPR',
VALUE => 'IN(SELECT OBJ_NM FROM ObjectsInfo_temp_table)',
object_type => 'TABLE');
**ObjectsInfo_temp_table is global temp table

i am getting error
ORA-31655: no data or metadata objects selected for job

when i try same thing with normal table it works ....am i missing somthing here..please help me

Sandhya
Re: data_pump name_expr [message #465508 is a reply to message #465493] Wed, 14 July 2010 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use a GTT.

Regards
Michel
Re: data_pump name_expr [message #465597 is a reply to message #465508] Wed, 14 July 2010 23:16 Go to previous messageGo to next message
sanju_chenna
Messages: 5
Registered: June 2010
Junior Member
HI Michel,
thanks for response.
I was trying tune my procedure as it is taking lot of time.
there is a select statement(with 4 joins) has to be executed 3 times for different reasons...i tried to keep those results in GTT and use them ....now as we can't use GTT ...can you please suggest any other method for tuning.

Sandhya

[Updated on: Wed, 14 July 2010 23:18]

Report message to a moderator

Re: data_pump name_expr [message #465634 is a reply to message #465597] Thu, 15 July 2010 01:04 Go to previous messageGo to next message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a permanent table to store your result.

Regards
Michel
Re: data_pump name_expr [message #465640 is a reply to message #465634] Thu, 15 July 2010 01:11 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
OR How about GTT with Preserve Rows option ?
Re: data_pump name_expr [message #465705 is a reply to message #465640] Thu, 15 July 2010 06:37 Go to previous messageGo to next message
sanju_chenna
Messages: 5
Registered: June 2010
Junior Member
HI,
thanks for you response Michel and rahul

michel,
I tried using permanent table before even posting to this forum(GTT problem)...actually it took equal time not much difference...and also we have to create one more table ... Sad.
by using this permanent table only i came to confirmation that problem is with GTT(i.e name_expr is not considering temp tables)...

Rahul,
I used GTT with preserve rows option only..still it did'nt work

sandhya
Re: data_pump name_expr [message #465736 is a reply to message #465705] Thu, 15 July 2010 08:23 Go to previous message
Michel Cadot
Messages: 58847
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
actually it took equal time not much difference

This does not surprised me, the main problem is on the query you use.

Regards
Michel
Previous Topic: FTP Dump file Over Network
Next Topic: sqlldr disable truncate
Goto Forum:
  


Current Time: Wed Aug 20 10:23:18 CDT 2014

Total time taken to generate the page: 0.10654 seconds