OraFAQ Forum: Server Utilities » Is it possible to change table name while import ?

Home » RDBMS Server » Server Utilities » Is it possible to change table name while import ? (Oracle,10.2, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Is it possible to change table name while import ? [message #439563]

Mon, 18 January 2010 07:01

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

ramoradba
Messages: 2457
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

Report message to a moderator

Re: Is it possible to change table name while import ? [message #439687 is a reply to message #439570]

Tue, 19 January 2010 01:47

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

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Is it possible to change table name while import ? [message #442291 is a reply to message #439694]

Sat, 06 February 2010 10:11

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

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?

Report message to a moderator