Link to home
Start Free TrialLog in
Avatar of medamadhu
medamadhu

asked on

imp in 11g to scott user

we are in process of upgrading our oracle database from 9i to 11g.I have created a separate database on 11g and imported 9i schema to 11g.Initially i imported the data using sys/system but could find the data and later i did the import from user called scott.Now i am in dilemma as what will happen if the end user get's connected to the 11g database,will he be able to access the data.I am really confused as i have deadline tomorrow and the 11g goes to production. Can somebody please help.
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

When you import, either import AS the same user, or use the fromuser/touser import options.

Which user was the export created as? Pre-create that user on 11g, grant it privileges, then run import as that user.

imp scott/tiger file=foo.dmp
Avatar of medamadhu
medamadhu

ASKER

mrjoltcola,

Export was created from scott user and i have imported to the same user scott.But i am just wondering what happened to all the data when i used  sys/system user.Why i am not able to find the data.
Hi,

You can use sys or system user to export any data from Your 9i database, just before import create all needed users in the 11g database and import using sys or system user.

Or You can export all user schemas  without data:

 exp sys/manager owner=user1,user2,user3 rows=n

if You have many users one of the method can be using parameter file with listing of all users, You can use select statement like "select username||',' from dba_users where username not in ('SYS','SYSTEM',"OUTLN');" for examle
import it into 11g

then

exp sys/manager owner=user1,user2,user3

it will export everything with data and then

imp sys/manager full=y ignore=y

it will import all data to the tables.


ASKER CERTIFIED SOLUTION
Avatar of mrjoltcola
mrjoltcola
Flag of United States of America image

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