Export / Import question (User definitions)

[JMac]

Building new version of an application and its DB.
Old version is 8.1.5.0, new version will be 9.2.0.4.0

I'll create a target DB in the 9R2 system. New scripts will be run to create new versions of existing (8i) tables - the new tables have additional columns etc.

I want to export one schema only (so USER=username is in the parameter file). A user specific export does not export User definitions, according to the docs.

When I import into my target database will I have to manually recreate all the Oracle user accounts of the users, and grant all the privileges to them all over again?

I don't want to use a FULL=Yes parameter as I'm creating new tablespaces for the tables in my 9R2 scripts (and the table structures differ too of course).

[marist89]

object level privs come over in a user level export. pre-create your users and give them appropriate system privs. Maybe now would be a good time to create a "SCHEMA_OWNER" role that had appropriate system privs...

[Mr.Hanky]

What about...

IGNORE=Y

[marist89]

Originally posted by Mr.Hanky
What about...

IGNORE=Y

and how does that help?

[robertbalmer]

I use
SQL>exp system/*****@bee123 file=my_8i_dump owner=(my_user,other_users)

SQL>imp system/*****@qwerty123 file=my_8i_dump.dmp fromuser=my_user touser=my_user

[robertbalmer]

Ooops

file=my_8i_dump owner=(my_user,other_users)
left out .dmp
file=my_8i_dump.dmp owner=(my_user,other_users)

[Mr.Hanky]

Originally posted by JMac

I don't want to use a FULL=Yes parameter as I'm creating new tablespaces for the tables in my 9R2 scripts (and the table structures differ too of course).

So couldn't you use IGNORE =Y, this way the existing structures (tablespaces included) will be ignored upon import.

[JMac]

... is a very good point/suggestion.

If it ignores the fact the tables already exist and just imports the data then that'd be fine. (I'm not that proficient with Imp/Exp )

Let me give it a go later this week.