Home » RDBMS Server » Backup & Recovery » Importing only tables from the dump file
Importing only tables from the dump file [message #220306] Tue, 20 February 2007 03:13 Go to next message
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.
Re: Importing only tables from the dump file [message #220310 is a reply to message #220306] Tue, 20 February 2007 03:36 Go to previous messageGo to next message
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
Re: Importing only tables from the dump file [message #220323 is a reply to message #220310] Tue, 20 February 2007 04:54 Go to previous messageGo to next message
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.
Re: Importing only tables from the dump file [message #220334 is a reply to message #220323] Tue, 20 February 2007 05:50 Go to previous messageGo to next message
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
Re: Importing only tables from the dump file [message #220337 is a reply to message #220323] Tue, 20 February 2007 05:51 Go to previous message
Mahesh Rajendran
Messages: 10672
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.
Previous Topic: small doubts on export/import
Next Topic: archived log file on network share?
Goto Forum:
  


Current Time: Sat Dec 03 10:02:31 CST 2016

Total time taken to generate the page: 0.05833 seconds