OraFAQ Forum: Server Utilities » Import DMP file to new Table

Home » RDBMS Server » Server Utilities » Import DMP file to new Table (Oracle 11)

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

Import DMP file to new Table [message #648187]

Thu, 18 February 2016 05:32

abhayman
Messages: 37
Registered: August 2011
Location: CA

Member

Hi,

I was able to export the table using below command

exp usr1/user1@tes tables=table1 file=/myfoler/export/parties.dmp log=/myfoler/export/parties.log statistics=none direct=y

But when I am trying to import this dmp into a new table on same schema I get error.

imp usr1/user1@tes file=/myfoler/export/parties.dmp buffer=131072 commit=y log=/myfoler/export/parties_imp.log feedback=1000 ignore=Y tables=export_test

I am getting below error while doing so IMP-00033: Warning: Table "EXPORT_TEST" not found in export file

Can someone help me where I am going wrong ? I want to import the dump into new table which is already created .

Regards.

Report message to a moderator

Re: Import DMP file to new Table [message #648191 is a reply to message #648187]

Thu, 18 February 2016 06:05

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

Senior Member
Account Moderator

You cannot import into another table than the one that has been exported (another schema, yes, but not another table name).
Use Data Pump instead.

Report message to a moderator

Re: Import DMP file to new Table [message #648194 is a reply to message #648191]

Thu, 18 February 2016 06:14

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

exp . . . tables=table1 . . .

imp . . . tables=export_test

Table "EXPORT_TEST" not found in export file

Might it be that table 'EXPORT_TEST' is not found in the export file because that is not the table you specified in your exp command .... ?????

Report message to a moderator

Re: Import DMP file to new Table [message #648204 is a reply to message #648191]

Thu, 18 February 2016 08:59

abhayman
Messages: 37
Registered: August 2011
Location: CA

Member

Hi,

We would need sys access to the DB right to proceed with Data Dump ?

As when I am trying to create dump directory CREATE DIRECTORY dmpdir AS '/users/test/export' I get ORA-01031: insufficient privileges error.

Is there anyway to do without creating directory ?

Regards,
Abhay

Report message to a moderator

Re: Import DMP file to new Table [message #648210 is a reply to message #648204]

Thu, 18 February 2016 09:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Is there anyway to do without creating directory ?
yes, use existing DIRECTORY

Report message to a moderator

Re: Import DMP file to new Table [message #648211 is a reply to message #648204]

Thu, 18 February 2016 09:44

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

Senior Member
Account Moderator

No, the directory is mandatory.
Ask you DBA to create one and grant you the access.
Otherwise your solutions are to:
* import into the same table then move the data into the target one (using INSERT SELECT
* import into another account (same table) and move the data into the target one (the first part requires DBA privileges)
* and other things like that using RENAME at one time or another one, depending in the existence or not of the exported table and data in this table and target one.

[Updated on: Thu, 18 February 2016 09:44]

Report message to a moderator

Re: Import DMP file to new Table [message #648212 is a reply to message #648211]

Thu, 18 February 2016 09:48

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

Senior Member
Account Moderator

Thinking a little bit more, I never tried it and maybe this will be a good thing you could bring to the community:
* create a synonym for the target table with the name of the source table and try to import (with ignore=yes)

Does this work?

[Updated on: Thu, 18 February 2016 09:48]

Report message to a moderator

Re: Import DMP file to new Table [message #648362 is a reply to message #648212]

Mon, 22 February 2016 04:08

cookiemonster
Messages: 13967
Registered: September 2008
Location: Rainy Manchester

Senior Member

It looks suspiciously like the two tables are in the same schema in the same DB. Is that the case?

Report message to a moderator

Previous Topic:	Convert DB2 load scripts to Oracle Load scripts
Next Topic:	loader utility - slow performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 23 08:28:56 CDT 2025