Home » RDBMS Server » Server Utilities » Import DMP file to new Table (Oracle 11)
Import DMP file to new Table [message #648187] Thu, 18 February 2016 05:32 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
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 Go to previous message
cookiemonster
Messages: 13966
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:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 17:08:19 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.