Sooner or later it will come to a situation in which we have to transfer data between different database. Given the fact we have to do a normal database upgrade Oracle Export/Import will help us to do as long we will go with a full database export and all users, priviledges, permissions and quota will get transfered.

Now given the case that we based on schedules or migration plans just have to overtake single schemas world does look different. Oracle Import will expect theuser schema already exisiting and even try to create all database objects in relation to their old location within the former database.

Now I’m not going to explain you how to change tablespace or storage information within this posting … perhaps in another one … but will try to help you with pre-setting up the schema and additional security information before importing the data.

As for we work with some database system all data can for sure get queried and even stored within directly executable sql statement. Since years I’m working with an easy to use set of scripts collecting these information for me.

Feel free to download it from here and using it for your own pleasure.  When unpacked you can call the script 0_capture_security_information.sql within SQLPLUS and you will get generated another result set of scripts containing all users, roles and granted system and role priviledges stored with the database you are connected too.

Connecting now to the new target database and executing the scripts full or in parts will allow you to set up security there again.