• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1307
  • Last Modified:

how to import dump except one table

i have a dump file.i dont want one table to be imported into the schema.how to do that?
0
chaitu chaitu
Asked:
chaitu chaitu
  • 11
  • 6
1 Solution
 
edster9999Commented:
The easiest way is to edit the file and remove that section
0
 
chaitu chaituAuthor Commented:
u mean dump file.
0
 
chaitu chaituAuthor Commented:
it is 4 MB file. its not opening
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
sujith80Commented:
Editing the file can corrupt the dump.
If yor are using conventional exp/imp then, Mention all the required tables by using the TABLES keyword.

imp SYSTEM/password TABLES=(TABLE1, TABLE2, TABLE3....)


If you are using data pump, use the EXCLUDE parameter
0
 
chaitu chaituAuthor Commented:
i am using conventional imp only but i think its very difficult to mention all the tables cos we have so many tables.

what is data pump?
0
 
sujith80Commented:
data pump is the advanced version of exp/imp, released along with 10g.
See this guide for more details.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/toc.htm
0
 
chaitu chaituAuthor Commented:
imp emp/VMI file=emp.dmp log=emp.log fromuser=emp touser=emp ;

how to write impdp command for above statment
0
 
sujith80Commented:
If you are on 10g and want to try to use data pump you may use the following steps.

First create a directory object where your dump files will be created. (THis has to be done from sqlplus)

create directory dump_dir as 'D:\spool';

Now export the tables. (Done from the OS prompt).

expdp <user>/<password>@<conn string> directory=DUMP_DIR DUMPFILE=xyz.dmp LOGFILE=xyz.log EXCLUDE=TABLE:"= 'YOUR TABLE TO EXCLUDE'"

You will have the dumpfile created in the directory D:\spool.
(Replace the string YOUR TABLE TO EXCLUDE with your table name.)

You may import this file using the following command :

impdp <user>/<password>@<conn string> directory=DUMP_DIR DUMPFILE=xyz.dmp LOGFILE=xyz_imp.log FULL=y
0
 
chaitu chaituAuthor Commented:
i have already dump file just i want to import that dump to new SCHEMA.
impdp <user>/<password>@<conn string> directory=DUMP_DIR DUMPFILE=xyz.dmp LOGFILE=xyz_imp.log FULL=y EXCLUDE=TABLE:"IN (DEPT)"

what is DUMP_DIR ??
0
 
chaitu chaituAuthor Commented:
sorry understood
0
 
chaitu chaituAuthor Commented:
I ran this command in linux.i got following error.dump is located in this path:/home/oracle/dump;

bash-3.00$ impdp username/password DIRECTORY=/home/oracle/dump DUMPFILE=username_2008.dmp LOGFILE=username_2008.log EXCLUDE=TABLE:"IN ('DEPT')"

Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 18 November, 2008 12:18:19

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name /HOME/ORACLE/DUMP is invalid
0
 
sujith80Commented:
Before posting the error back, why dont you take a look at the steps?
Have you created the directory as below ???????????????

>> First create a directory object where your dump files will be created. (THis has to be done from sqlplus)
>> create directory dump_dir as 'D:\spool';
0
 
sujith80Commented:
You cannot reference the physical directory paths while using datapump.
YOu need to create a directory object, where the dump files will be created. This DIRECTORY object has to be created through an oracle user from sqlplus.

DIRECTORY object has to be created -
- Preferrably the same user doing the expdp and impdp
- Or by a super user like SYSTEM then grant the read and write to the user doing datapump empdp and impdp
0
 
chaitu chaituAuthor Commented:
i have created DIRECTORY object  from oracle user in linux like
mkdir dump_dir as '/home/oracle/dump_dir';
now directory is created .after that i placed the dump file in that directory.now when i run the impdp command i got the following error.

impdp username/password DIRECTORY=dump_dir DUMPFILE=username_2008.dmp LOGFILE=username_2008.log EXCLUDE=TABLE:"IN ('DEPT')"

Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 18 November, 2008 12:59:03

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DUMP_DIR is invalid



0
 
chaitu chaituAuthor Commented:
wait sujit i moved forward..
0
 
chaitu chaituAuthor Commented:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39143: dump file "/home/oracle/admin/dpdump/xyz.dmp" may be an original export dump file

i moved further.. i got above error.

i have one doubt.while exporting dbdump i used conventional exp command  while importing same dbdump can i use impdp command?
0
 
sujith80Commented:
>> mkdir dump_dir as '/home/oracle/dump_dir';
I have no comments on this, READ THE STEPS carefully

>> while exporting dbdump i used conventional exp command  while importing same dbdump can i use impdp command?
No, they both are different utilities. Not compatible.
0
 
chaitu chaituAuthor Commented:
this is problem i am facing right now;we got the db dump using conventional exp command.now i can't use impdp command to import the dump.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now