Though Oracle 10g provides new tools expdp and impdp for exporting and importing data between databases.
We still see lots of companies using old exp/imp to copy and move data. However, using exp/imp implies that DBA have to do more manual work in order to export and import data successfully. For example, tablespaces must be created before one can import dump to another database, privileges must be granted explicitly after the import and DBA have to create synonyms manually etc. All these are necessary to ensure users can use data on the new database without errors.
Here is a list of high level steps to copy schemas from one database to another.
In the target database (new database):
1). Create any roles for the user .Grant system/object/role privileges that the user currently have in the source database
2). Create any database links will be required in the schema.
3). Create tablespaces. These would be the same tablespace names that the user currently have in the source database.
4). Create profiles that the user currently using in the source database.
5). Create the user who own the schema. Grant the privs/roles to those user as required.
6). Set the correct tablespace quota limit for users
7). Perform schema exports from the source database.
8). Perform schema import into the target database.
9). Create synonym in other schemas that need to reference to objects in the new schema.
10). Create constraints in other schema that need to reference to objects in the new schema. e.g. Foreign key constraints
11).Recompile invalid objects
12). Check if there are still invalid objects and fix them.
Filed under:
Oracle