Difference between revisions of "(Re)create user/schema using DataPump"
From dbawiki
| Line 1: | Line 1: | ||
| + | Remember, one thing Datapump doesn't do (by design) is recreate grants to objects owned by SYS.<br /><br /> | ||
If the user does not yet exist on the destination database, just import it using the procedure outlined [[DataPump|here]].<br /> | If the user does not yet exist on the destination database, just import it using the procedure outlined [[DataPump|here]].<br /> | ||
Or if you are asked to create a user 'like' another user, use this: | Or if you are asked to create a user 'like' another user, use this: | ||
<pre> | <pre> | ||
| − | + | $ expdp schemas=CURRENTUSER content=metadata_only | |
| − | + | $ impdp remap_schema=CURRENTUSER:NEWUSER | |
</pre> | </pre> | ||
If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.<br /> | If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.<br /> | ||
| − | + | Run [[Extract ddl with "dbms metadata.get ddl"#Get User DDL with related grants|this script]] to generate the recreation DDL<br /> | |
| − | After this | + | After this has been run, the following command can be executed: |
| − | + | <pre> | |
| + | drop user <schema name> cascade; | ||
| + | </pre> | ||
Now, recreate the schema with the above-mentioned DDL<br /> | Now, recreate the schema with the above-mentioned DDL<br /> | ||
The dumpfile (or a part thereof) can now be imported to fill up the currently empty schema: | The dumpfile (or a part thereof) can now be imported to fill up the currently empty schema: | ||
| − | + | <pre> | |
| + | impdp <usr>/<pwd> parfile=schema.par | ||
| + | </pre> | ||
where schema.par would look like this: | where schema.par would look like this: | ||
| − | + | <pre> | |
| − | + | directory=DATA_PUMP_DIR | |
| − | + | dumpfile=<schema_name>.DMP | |
| − | + | schemas=(<schema_name>) | |
| + | </pre> | ||
and/or maybe one or more of the following: | and/or maybe one or more of the following: | ||
<pre> | <pre> | ||
exclude=grants | exclude=grants | ||
| − | tables=(< | + | tables=(<schema_name>.<table_name>) |
remap_tablespace=old:new | remap_tablespace=old:new | ||
</pre> | </pre> | ||
Finally, recompile all invalid objects: | Finally, recompile all invalid objects: | ||
| − | + | <pre> | |
| − | + | exec utl_recomp.recomp_serial(); | |
| − | + | or | |
| − | + | exec utl_recomp.recomp_parallel(4); | |
| − | + | or | |
| − | + | @?\rdbms\admin\utlrp.sql | |
| − | + | or | |
| − | + | @?\rdbms\admin\utlprp.sql(4) | |
| − | + | </pre> | |
| − | |||
| − | |||
| − | |||
Revision as of 20:29, 15 April 2013
Remember, one thing Datapump doesn't do (by design) is recreate grants to objects owned by SYS.
If the user does not yet exist on the destination database, just import it using the procedure outlined here.
Or if you are asked to create a user 'like' another user, use this:
$ expdp schemas=CURRENTUSER content=metadata_only $ impdp remap_schema=CURRENTUSER:NEWUSER
If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.
Run this script to generate the recreation DDL
After this has been run, the following command can be executed:
drop user <schema name> cascade;
Now, recreate the schema with the above-mentioned DDL
The dumpfile (or a part thereof) can now be imported to fill up the currently empty schema:
impdp <usr>/<pwd> parfile=schema.par
where schema.par would look like this:
directory=DATA_PUMP_DIR dumpfile=<schema_name>.DMP schemas=(<schema_name>)
and/or maybe one or more of the following:
exclude=grants tables=(<schema_name>.<table_name>) remap_tablespace=old:new
Finally, recompile all invalid objects:
exec utl_recomp.recomp_serial(); or exec utl_recomp.recomp_parallel(4); or @?\rdbms\admin\utlrp.sql or @?\rdbms\admin\utlprp.sql(4)
