OraFAQ Forum: Backup & Recovery » Importing only tables from the dump file

Home » RDBMS Server » Backup & Recovery » Importing only tables from the dump file

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

Importing only tables from the dump file [message #220306]

Tue, 20 February 2007 03:13

prashas_d
Messages: 66
Registered: February 2007

Member

Hello gurus,

Can anyone please let me know how to do the below task?

I have a dump file that contains the entire tables, procedures, sequences, etc of one instance of database.

I need to take only the tables from the dump file and import into another instance of database.

If I need to take one table, I will use the statement as:

imp scott/tiger file=expdat.dmp table=emp log=test.log

But for importing all the tables(only tables)from the dump file to another instance of database, what is the command that I need to run on UNIX?

Thanks in advance.

Report message to a moderator

Re: Importing only tables from the dump file [message #220310 is a reply to message #220306]

Tue, 20 February 2007 03:36

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,

Use TABLES parameter with your import files.
and create .SQL file for TABLES parameter

regards
Taj

Report message to a moderator

Re: Importing only tables from the dump file [message #220323 is a reply to message #220310]

Tue, 20 February 2007 04:54

prashas_d
Messages: 66
Registered: February 2007

Member

Taj,

Sorry, I could not able to get your point.

What value should I use in imp command for TABLES parameter?
imp scott/tiger file=expdat.dmp tables=<what value>

Also, How to create a .SQL file using that dump file?

Can you please tell me in detail about how to do it.

I am new to the database concepts so could not able to get your point.

Report message to a moderator

Re: Importing only tables from the dump file [message #220334 is a reply to message #220323]

Tue, 20 February 2007 05:50

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
Tables = if you know tables name put in tables parameter like
IMP SCOTT/TIGER@ORCL FILE=C:\SCOTT.DMP TABLES=(EMP,DEPT,BONUS)

OR
CREATE INDEXFILE ( .SQL) OR RUN

SQL> host imp oe/oe@orcl file=oe.dmp indexfile=c:\dummy.sql indexes=n constraint
s=n grants=n  full=y

it is DUMMY import actually it is not import anything in database.
but it create .sql file specify by INDEXFILE paramter location.

just edit "delete REM" and run on SQL prompt.

try let me know if any issue.

regards
Taj

Report message to a moderator

Re: Importing only tables from the dump file [message #220337 is a reply to message #220323]

Tue, 20 February 2007 05:51

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

oracle@mutation#imp scott/tiger fromuser=scott touser=scott tables=% file=scott.dmp

Import: Release 9.2.0.7.0 - Production on Tue Feb 20 06:51:22 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                   "PLAN_TABLE"          0 rows imported
Import terminated successfully without warnings.

Report message to a moderator

Previous Topic:	small doubts on export/import
Next Topic:	archived log file on network share?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 20:27:45 CST 2024