Difference between revisions of "(Re)create user/schema using DataPump"

Revision as of 14:43, 20 December 2011

If the user does not yet exist on the destination database, just import it using the procedure outlined here.

If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.
So before dropping the schema, run this script to generate the correct DDL beforehand!

and the grants are different on the destination database than they were on the source database, they should be retained. This can be accomplished by excluding grants when importing.

drop user <schema name> cascade;

The schema should ideally be checked beforehand that it does not own objects like 'type's that may exist elsewhere...

Import the schema:

impdp <usr>/<pwd> parfile=schema.par

where schema.par would look like this:

schemas=<schema name>
exclude=grants

Finally, recompile all invalid objects:

exec utl_recomp.recomp_serial();
or
exec utl_recomp.recomp_parallel(4);
or
@utlrp.sql
or
@utlprp.sql(4)