Link to home
Start Free TrialLog in
Avatar of HenryBStinson
HenryBStinson

asked on

Parameters FROMUSER and TOUSER in Oracle IMP (import)

I'm trying to import a copy (DMP file) of the Millennium schema from the A0218174 into the Oracle database on my Laptop, but I'm confused about how to use the TOUSER parameter.
 
I'm looking at the documentation for the Oracle command line "imp" import utility
and am following most of the information on how to set the parameters, but I'm confused about how to bring in the Millennium schema properly.
 
It looks like I'm supposed to use DOS command:
 
    imp parfile=parameters.dat
 
 (where I'm defining some parameters in the parameters.dat file)
 
then give it USERID: "sys as sysdba" when it asks for it,
 and then the password (which I won't repeat here).
and the filename ("EXPDAT1.DMP" in my case)
 
   -- I assume that I have to run the command line "imp" while the DOS window's default directory is set to the directory containing that DMP file.
 
I assume that I should give it the parameter (this is in the parameters.dat file):
 
       FROMUSER(MILLENNIUM)
 
(Actually, I read on the Internet that the syntax for this is supposed to be like:
 
      FROMUSER=MILLENNIUM
)
 
-- where "millennium" is the name of the schema from which it was exported in the Oracle database at our client's site in Texas, and to which it should be imported in the Oracle database on my laptop.  Millennium is also the schema owner's name.
 
Does using the parameter "FROMUSER=MILLENNIUM" assume that the user ID used to export the Millennium schema was "millennium"?  What if the user who exported the .DMP file (which I assume contains only the millennium schema)  was "system" or "sys" or some other user name?
 
Does it matter what user ID the person who exported the file used?
 
In my parameters file, I also had the line "TOUSER(MILLENNIUM)"
but I got an error saying that user did not exist.  
-- Do I first have to create a user named "millennium" in the instance of Oracle on my laptop?
Avatar of seazodiac
seazodiac
Flag of United States of America image

-- Do I first have to create a user named "millennium" in the instance of Oracle on my laptop?


Yes, of course
ASKER CERTIFIED SOLUTION
Avatar of baonguyen1
baonguyen1

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HenryBStinson
HenryBStinson

ASKER

It turns out that I did have to create the user "millennium" before I could import into a schema that will be owned by that user, using the "TOUSER" syntax.  There was not enough information (for me) in either of the above answers; although I appreciate the length of the second reply.  Both left me hanging because of my own ignorance about how to set parameters (such as tablespace) in the CREATE USER command.  I'm going to accept the first solution, because that was the gist of the problem.  
I'm posting here what I learned.   I'm going to be annoyingly wordy and repetitive, so hang with me.

One thing you can read on the Internet and from the Oracle documentation is that before doing an import, one has to run a couple of SQL scripts:  catalog.sql and catproc.sql.  No-where did I find any instructions on how to run these scripts.  They are both very long and extremely complex.  Copying them into SQLPlus (GUI version) did not work -- even after replacing all the "rem" and "remark" keywords with "---".  Toad would not run them.  The SQL Scratchpad tool in Oracle Enterprise Mgr would not run them.  

Then I found that to run them, log into the command line version of SQLPlus (as a DBA, such as "SYS AS SYSDBA") and at the "SQL>" prompt, type "@catalog.sql" -- let that run, and then type "@catproc.sql".  That worked fine.  

NOTES: Those scripts only need to be run once for a database, and they set up a lot of views and stored procedure packages necessary for an import to work.   They take a long time to run.  Good time to go out and get a cup of coffee after you make sure they are running OK.

I then received advice to create a user called MILLENNIUM before importing the schema (coincidentally named MILLENNIUM) into the Oracle instance on my laptop (where user "MILLENNIUM" was the owner of the MILLENNIUM schema in the source database and would be the owner of the MILLENNIUM schema in the target database).  The person who exported the DMP file sent me this:
=============================================================
1. Create the user "millennium" and set password to <password not given here>. give it a default tablespace which has enough space. this could be the tablespace USER (I assume he meant "USERS"), but make sure it can expand. i would then GRANT DBA TO MILLENNIUM via sqlplus.  
2. Then from a dos command window where the dmp file is located enter
imp millennium/<password> fromuser=millennium touser=millennium file=expdat1.dmp ignore=yes
=============================================================
Another website (I hope I can quote this -- http://www.psoug.org/reference/user.html --)
had this example:
    CREATE USER uwclass
    IDENTIFIED BY "N0Way!"
    DEFAULT TABLESPACE data_sml
    TEMPORARY TABLESPACE temp
    QUOTA 0 ON SYSTEM
    QUOTA UNLIMITED ON data_sml
    QUOTA 10M ON indx_sml;
 
from which I determined that the command I would need to use would be something like:
 
     CREATE USER 'MILLENNIUM' IDENTIFIED BY '<password>'
     DEFAULT TABLESPACE    USERS
     TEMPORARY TABLESPACE  TEMP
     QUOTA 0 ON SYSTEM
     QUOTA UNLIMITED ON USERS
     QUOTA  20M ON indx_sml;
 
but I was confused by what to do about the term "indx_sml" in the example.  Where did that come from and what does it mean?
It seems to be an allocation of tablespace for use as indexes, but how would Oracle know what that meant?  

That turned out to be a moot point.  I found someone who talked me through the process of creating the necessary owner-user and to then do the import.

He said to ignore the default tablespaces issue and not to use SQLPLUS (command line version) to create the user, but to instead use Oracle Enterprise Manager.  (I was glad to be able to use a GUI, instead of a command line utility for once.)  

In Ent. Mgr., I logged onto the target database (on my laptop) as "SYS AS SYSDBA".   Then I was directed to open the "Security" folder/icon under the target database name, and then open the "Users" folder/icon.   From there I right clicked to create a new user named "millennium".  There were defaults set for me -- USERS as default tablespace, and I was told to ignore the rest of the options and to go to the "Roles" tab and assign "DBA" to the new user.  I found I had to not only select "DBA" from the list of roles but had to double-click that role so that it showed up in the list-window below before clicking "Apply" or "OK".

I had to
1. Make sure the parameters file (parameters.txt) and the data export file (EXPDAT1.DMP) were in the same directory.
2. Open a DOS window.
3. Navigate to the directory containing those files.
4. Run the command line import function "IMP" as:

    imp millennium/<password> parfile=parameters.txt

where the parameters.txt file contained this:

FROMUSER=MILLENNIUM
TOUSER=MILLENNIUM
FILE=EXPDAT1.DMP
LOG=IMPORT.LOG
IGNORE=YES

Note that when I executed "IMP",    
     imp millennium/<password> parfile=parameters.txt
I gave it a user ID = "millennium", which I created beforehand and gave DBA role privileges.

In this case, "millennium" is a special user ID who not only "owns" the millennium schema but must have all dba-like privileges on that schema, including creating stored procedures, creating indexes, doing exports and imports, etc.  

One interesting thing is that I got a lot of error messages from the "IMP" utility about "missing roles".  These were roles that seemed to be referenced in the DMP file.  The person talking me through the import process told me to ignore all those "missing role" error messages, and the import seemed to proceed normally otherwise.  I do not need those roles for now, but at some point I will have to add them, so I will contact the manufacturer of the product that uses this database to obtain a partial DDL script to create all the necessary user roles.  My VB application does not need them, but the other (mfrs) application will.

I apologize that this information is so wordy, but I hope this helps some others looking for similar answers.