Import Oracle dump file

check which type of import you have taken.


steps

first generate a logfile to knw wat are the contents in it.

 using,
 
cmd > imp system/passwd file=c:\qds.dmp log=c:\import.log  show=y

 let it run,then you will find the logfile genertaed in the path,the check out whtr you are going for a schema level import or a full database level import.

 Import differs in ,
  Full database level import == > you have to verify with the logfile wheather you have necessary path ( i.e c:\ d:\ e:\ ) and in that you should create the specified folders.

 once after you verify all the exact folders present then go with the import using

 cmd  > imp system/passwd file=c:\qds.dmp log=d:\fullimp.log full=y

 Schema wise (user wise )  ===> you have to create the necessary tablespaces mentioned in the logfile which you generated at the top.but path may vary,then create the users ,then issue,
 
 imp system/passwd file=c:\qds.dmp log=user.log fromuser=username in dumpfile touser= username to be imported



Also refer the following site to knw more abt import parameters,
 
 http://www.psoug.org/reference/import.html

see help: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#i1023560

do you have log of export? or do you know if export was done in Full, User or Table mode?
be carefull about characterset while importing - if you use different characterset during import of export character set, your data might be corrupted

You cannot use Oracle's import utility to "create" a database.  A database must be created first, before you can run import.  This can be a starter or empty database, or it could already have other data in it.  What import can do is load in all of the data and other objects (user accounts, indexes, constraints, views, PL\SQL objects, etc.) that were exported, depending on the type of export that was done.

A couple important things to know about import:
1. It will try to create the objects (tablespaces, tables, index, etc.) in exactly the same place (disk, directory and tablespace) they were in the server where the export was done.  If you want or need some of these to be in different places on this server, you must create them manually before running import, then use the "ignore=Y" parameter with import so it will use the existing objects that you created, and not error out.
2. Import can *ONLY* do inserts of table data!  It will never do deletes or updates.  So, you cannot use import by itself to refresh an existing database that already contains much of the data that is in the export file.  You could manually drop or truncate the tables that you know are in both, then let import load in the data.  The new "DataPump Import" (impdp) of Oracle10 can truncate existing tables if you tell it to - but that is not the default, and the "old" version of import (imp) cannot do this.

I have a database created on my Oracle. I cannot use the datapump, as the dba who perorfmed the export did not. I have to use the imp utilitiy. So far I have not been able to get it to work properly, an I do not have the dump file's export par file. I'm still working this, but did not have the time to put much time into it. I will be this weekend. Thanks for the comments. I'll try it all and see what I can get done.

Personally, I find the older "imp" easier to work with than the new "DataPump" (or impdp).  Maybe that is just because I have used imp for at least 12 years, or because it only requires a few options.  The new DataPump is faster and more flexible, but that means you have to provide more parameters.

I hope your version of Oracle is at least as high as that of the database where the export was not.  If not, you will not be able to import the file.

I am using 10g, and the dump file came from a 10g database. I open a command line dialog and open the imp utility. It asks for a user name. I enter it and it asks for a password, which I put in. It then brings me to this prompt: Import file: EXPDAT.DMP >
I then enter the name of my dump file, mydatabase.dmp. It then asks for me to enter a buffer size. I am not sure how much to enter. I just put the minimum. It then warns me that the objects were created by SYSTEM, not by you. It than asks: List contents of import file only, I enter yes. It then asks Import entire export file, I enter yes. It then runs, and taks about a minute. It does not give me any messages. It just runs then closes itself. I go back into oracle, and I can't find any tables or where anything was created. WHat am I doing wrong?

I tried it again and got something. The final line said "Import terminated successfully with warnings."  The text of my import log after this is below. I'm not sure what it all means. The tables are not in the database yet either.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

use

please don't import the dmp as system cause all tables will reside in system tablespace and that will lead to problems.

first create new user : as

1- connect as system : conn system/password

run this after modifying the parameters (


CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "*******" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK
GRANT "CONNECT" TO "TEST"
GRANT "RESOURCE" TO "TEST"


2- then

connect as test or what ever the user name u modified ..

3- import the file normally using the new users

imp  test/password  file = path

thank you

No!  There is no problem with running the import as the SYSTEM user!  That will definitely *NOT* put all tables into the system tablespace!  Import will try to put every table into the same tablespace it was in in  the database where the export was done.  This is true regardless of which user did the export or the import.

You can run import either interactively (as you tried) or with a parameter file to provide the parameters (this is the approach I usually use, but either way can work).

If you run import interactively, just use the suggested default value for: "buffer size".  I think your biggest problem was the next question: "List contents of import file only?"  where you said that you repiled: "yes".  That tells import to *NOT* actually import anything!  Instead, the contents of the file (tables and indexes, but no table data) just get written to the log file.  This allows you to then open the log file with a text editor to cofirm the names of the objects in the *.dmp file for example.

Run import again, but say "no" this time to the question: "List contents of import file only?".

markgeer:
Thanks for he info. I still cannot get it to work. I did the following:
Import File: EXPDAT.DMP > artemi_schema.dmp
List contents of import file only (yes/no): no > no
Ignore create error due to object existence (yes/no): no > yes
Import grants (yes/no): yes > yes
Import table data (yes/no): yes > yes
Import entire export file (yes/no): no > yes
Once I do this, it quickly gives me an error and shuts down. It shows the error so fast and shuts down I cannot see it. What am I doing wrong here?Any ideas?

Now I am getting a read error:

IMP-00002: failed to open artemis_schema.dmp for read

Now I am really lost.

Import will create a log file when it runs.  (This is a plain ASCI text file that yo ucna open with any text editor.)  Look for that in your current working directory.  That should have the error text in it.

Are you in the directory where that file is?  If not, you will have to provide the full path, not just the file name.

I am in the directory where the file is. I could not find a log file as it will not run. Whatever error it is stops the process before anything really happens. I was able to run something like anand_2000v suggested: cmd > imp system/passwd file=c:\artemis_schema.dmp log=c:\import.log show=y full=y.
It then ran without importing and posted an import log to the directory. It does not specify any errors. The last line in the file says "Import terminated successfully without warnings." It will read the file if I say yes to List contents of import file only. But when I try to do the import and say no to that question, I get a very quick error and it shuts down. Can it have something to do with the user? Do I need to do the import a different way?

If that command worked, then this should also and it should give you a log file (C:\import.log) that you can then read:
imp system/passwd file=c:\artemis_schema.dmp log=c:\import.log full=y.

That is a correct way to run import.  I usually run it with a parameter file like this:

imp system/passwd parfile=imp_full.ctl

Here is an example of a parameter file I used: (named: "imp_full.ctl")

file=(H:\export\expfull.dmp)
log=G:\trace\imp_full.log
full=Y
rows=Y
indexes=Y
ignore=Y
commit=Y

Alright, I did what you suggested in your last post. I created a parameter file. I am still getting an error, however it's telling me a user does dont exist. Is this correct? What user does not exist?


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. importing SYSTEM's objects into SYSTEM
. importing SMLA's objects into SMLA
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.

markgeer:
Alright, I created the SMLA user and granted the permissions. It worked. Finally. It ran just I was led to believe it would. I did still encounter errors, specifically Oracle error 6550. I can't seem to find that error anywhere. What is that error? It did import in over 70 tables, but I'm not sure how many were in the orginal schema. Thanks for all of the help.