Home » RDBMS Server » Server Utilities » Is it possible to change table name while import ? (Oracle,10.2, Linux)
Is it possible to change table name while import ? [message #439563] Mon, 18 January 2010 07:01 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
I wanted to export a table "emp_production" from Production database then import it as "emp_datawarehouse" in Data warehouse database.
Both tables has same structure.
I have granted IMPORT FULL DATABASE & EXPORT FULL DATABASE privileges to both schema

I tired with the following syntax

$ Expdp u1/p1@h1[/email] tables= emp_production directory=test dumpfile=test1.dmp

$ Impdp u1/p2@h2[/email] directory=test dumpfile=test1.dmp remap_schema=u1.emp_production:u2.emp_datawarehouse
remap_tablespace=Example1:Example2


But I am getting the following error
ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping.

Why this ?
"emp_production" table has 150 million rows, every week importing this table then inserting into "emp_datawarehouse" table takes long time.

Any idea please.

Thanks in advance
sbmk

[Updated on: Mon, 18 January 2010 07:35]

Report message to a moderator

Re: Is it possible to change table name while import ? [message #439570 is a reply to message #439563] Mon, 18 January 2010 07:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
see this

ORA-39122: Unprivileged users may not perform %s remappings.
 *Cause:  A user attempted to remap objects during an import but lacked the
          IMPORT_FULL_DATABASE privilege.
 *Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE
          privilege.


sriram Smile
Re: Is it possible to change table name while import ? [message #439687 is a reply to message #439570] Tue, 19 January 2010 01:47 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Import-schema alreary have IMPORT FULL DATABASE privilege.
(I am not using system user for impdp)
any other idea... Smile

Thanks
sbmk

[Updated on: Tue, 19 January 2010 02:02]

Report message to a moderator

Re: Is it possible to change table name while import ? [message #439694 is a reply to message #439687] Tue, 19 January 2010 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If Oracle says you have not this role, I tend to think you have not this role, check it again.

Regards
Michel
Re: Is it possible to change table name while import ? [message #442291 is a reply to message #439694] Sat, 06 February 2010 10:11 Go to previous messageGo to next message
entekeralam
Messages: 19
Registered: January 2010
Junior Member
You are trying to rename table in remap_schema... I think you can't do that in 10g. 11g has REMAP_TABLE

Your option is to import into diff schema and rename the table after that.

[Updated on: Sat, 06 February 2010 11:09] by Moderator

Report message to a moderator

Re: Is it possible to change table name while import ? [message #443001 is a reply to message #439563] Thu, 11 February 2010 23:54 Go to previous messageGo to next message
Blaylock
Messages: 6
Registered: February 2010
Location: USA
Junior Member
I have a large (multi-GB) data file exported from an Oracle table. I want to import this data into another Oracle instance, but I want the table name to be different from the original table. Is this possible? How?
Re: Is it possible to change table name while import ? [message #443002 is a reply to message #443001] Thu, 11 February 2010 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Is this possible? How?
I do not think so.
import with same name, afterwards rename the table.
Re: Is it possible to change table name while import ? [message #444096 is a reply to message #443002] Thu, 18 February 2010 12:29 Go to previous messageGo to next message
cristi_Buc
Messages: 12
Registered: February 2010
Location: Bucharest
Junior Member
You can create a DBlink and that solve the problem. It is easier to do it.
Re: Is it possible to change table name while import ? [message #444097 is a reply to message #444096] Thu, 18 February 2010 12:30 Go to previous message
cristi_Buc
Messages: 12
Registered: February 2010
Location: Bucharest
Junior Member
You can create a materialized view as well.
Previous Topic: SQL Loader : How can we make use of RECNUM in SQL expression
Next Topic: sqlldr and index growth problem
Goto Forum:
  


Current Time: Mon Dec 05 18:57:07 CST 2016

Total time taken to generate the page: 0.06378 seconds