Difference between revisions of "(Re)create user/schema using DataPump"
From dbawiki
| Line 1: | Line 1: | ||
If the user does not yet exist on the destination database, just import it using the procedure outlined [[DataPump|here]]. | If the user does not yet exist on the destination database, just import it using the procedure outlined [[DataPump|here]]. | ||
| − | If however, the user already exists and the grants are different on the destination database than they were on the source database, | + | If however, the user already exists 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... | 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: | Finally, recompile all invalid objects: | ||
| − | exec utl_recomp... | + | exec utl_recomp.recomp_serial(); |
| + | or | ||
| + | exec utl_recomp.recomp_parallel(4); | ||
| + | or | ||
| + | @utlrp.sql | ||
| + | or | ||
| + | @utlprp.sql(4) | ||
Revision as of 22:41, 30 November 2011
If the user does not yet exist on the destination database, just import it using the procedure outlined here.
If however, the user already exists 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)
