Solved

Parameters FROMUSER and TOUSER in Oracle IMP (import)

Posted on 2004-04-14
3
54,778 Views
Last Modified: 2011-08-18
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?
0
Comment
Question by:HenryBStinson
3 Comments
 
LVL 23

Expert Comment

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


Yes, of course
0
 
LVL 8

Accepted Solution

by:
baonguyen1 earned 50 total points
ID: 10826246
1. Does using the parameter "FROMUSER=MILLENNIUM" assume that the user ID used to export the Millennium schema was "millennium"?

No, it means MILLENNIUM is the owner of the object

2.  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?

No issue. SYS and SYSTEM have right to export database at any level (database, user or table). If your MILLENNIUM has export right you can use it to export.

3. Does it matter what user ID the person who exported the file used ?

See above. Only one issue is if you export for example a table that owned by other schema you have to specify:

TABLES=<schema>.<tablename>

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

Yes. It's required to use "TOUSER" option even you specify the millennium for both FROMUSER and TOUSER.

If you import a full database you may not need to pre-create the user

Hope this helps
0
 

Author Comment

by:HenryBStinson
ID: 10827936
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.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now