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?
LVL 20
chaitu chaituAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

edster9999Commented:
The easiest way is to edit the file and remove that section
chaitu chaituAuthor Commented:
u mean dump file.
chaitu chaituAuthor Commented:
it is 4 MB file. its not opening
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SujithData ArchitectCommented:
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
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?
SujithData ArchitectCommented:
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
chaitu chaituAuthor Commented:
imp emp/VMI file=emp.dmp log=emp.log fromuser=emp touser=emp ;

how to write impdp command for above statment
SujithData ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 ??
chaitu chaituAuthor Commented:
sorry understood
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
SujithData ArchitectCommented:
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';
SujithData ArchitectCommented:
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
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



chaitu chaituAuthor Commented:
wait sujit i moved forward..
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?
SujithData ArchitectCommented:
>> 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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.