Home » RDBMS Server » Server Administration » USER PRIVS Migration from 9i to 10g
USER PRIVS Migration from 9i to 10g [message #276969] Sat, 27 October 2007 10:19 Go to next message
seethem
Messages: 41
Registered: September 2007
Member
Gurus et al,

QUESTION
============
* How do I migrate all the roles/object & system privileges for a schema from 1 database to another?


BACKGROUND INFO
==================
I inherited a database with little to no documentation about how it was built, what roles, system privileges, object privileges to assign to the database users.

I am interested in moving a 9i enterprise edn database into a 10g enterprise edition database RAC.

Both sit on windows 2003 Enterprise.


IDEA
=======
* We currently are interested in using EXP/IMP method.

* On the 10g database recreate the schema. Assign all the necessary roles/system privileges to the schema.

* We are able to determine the session privileges (session_privs view)

* Unsure that this is the best method.


Please share what you think/ How it can be done?
Re: USER PRIVS Migration from 9i to 10g [message #276971 is a reply to message #276969] Sat, 27 October 2007 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
exp/imp full export and import all users, roles and privileges.

Regards
Michel
Re: USER PRIVS Migration from 9i to 10g [message #276978 is a reply to message #276971] Sat, 27 October 2007 11:43 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
Bare with me Michael please,

Basically, I use the EXP utility to export the database full.

Then, I am simply creating the same tablespaces, users, their roles and assigning the quota unlimited to their tablespaces on the new database. Just as it was on the old database.

Finally, I use the IMP utility to import into the schemas (from user to user). All the object privileges that were present for the database users in the old database would be present in the new?

Do I have the process correct.
Thanks in advance.
Re: USER PRIVS Migration from 9i to 10g [message #276983 is a reply to message #276978] Sat, 27 October 2007 11:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
import full reimport all users, roles and privileges.

Regards
Michel

[Updated on: Sat, 27 October 2007 11:56]

Report message to a moderator

Re: USER PRIVS Migration from 9i to 10g [message #277000 is a reply to message #276983] Sat, 27 October 2007 15:34 Go to previous messageGo to next message
seethem
Messages: 41
Registered: September 2007
Member
Is it possible to elaborate please?
Re: USER PRIVS Migration from 9i to 10g [message #277015 is a reply to message #276969] Sat, 27 October 2007 23:52 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Oracle Database Utilities->Original Export and Import.
Previous Topic: External Tables / System Tablespace
Next Topic: Upgrading from Standard to Enterprise
Goto Forum:
  


Current Time: Wed Dec 07 16:42:25 CST 2016

Total time taken to generate the page: 0.05894 seconds