(Re)create user/schema using DataPump - Revision history

Stuart at 12:01, 13 November 2013

2013-11-13T12:01:21Z

Stuart at 20:29, 15 April 2013

2013-04-15T20:29:25Z

Stuart at 23:00, 26 December 2012

2012-12-26T23:00:31Z

Stuart at 22:59, 26 December 2012

2012-12-26T22:59:45Z

Stuart at 22:57, 26 December 2012

2012-12-26T22:57:11Z

10.250.159.106 at 15:36, 20 December 2011

2011-12-20T15:36:31Z

10.250.159.106 at 15:34, 20 December 2011

2011-12-20T15:34:39Z

10.250.159.106 at 15:18, 20 December 2011

2011-12-20T15:18:12Z

10.250.159.106 at 14:53, 20 December 2011

2011-12-20T14:53:17Z

10.250.159.106 at 14:43, 20 December 2011

2011-12-20T14:43:03Z

@@ 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 [[Data Pump|here]].<br />
 Or if you are asked to create a user 'like' another user, use this:
 <pre>

@@ Line 1: / Line 1: @@
 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:
 <pre>
- $ expdp schemas=JOE content=metadata_only
+$ expdp schemas=CURRENTUSER content=metadata_only
- $ impdp remap_schema=JOE:NEWUSER
+$ impdp remap_schema=CURRENTUSER:NEWUSER
 </pre>
 If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.<br />
-But, before dropping the schema, run [[Extract ddl with "dbms metadata.get ddl"#Get User DDL with related grants|this script]] to generate the correct DDL beforehand!<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 DDL has been run and the results stored, the following command can be executed:
+After this has been run, the following command can be executed:
- drop user <schema name> cascade;
+<pre>
 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:
- impdp <usr>/<pwd> parfile=schema.par
+<pre>
 where schema.par would look like this:
- directory=DP_IMPORT
+<pre>
- dumpfile=<db_name>_DP.DMP
+directory=DATA_PUMP_DIR
- schemas=(<schema name>)
+dumpfile=<schema_name>.DMP
+schemas=(<schema_name>)
 and/or maybe one or more of the following:
 <pre>
 exclude=grants
-tables=(<schema name>.<table name>)
+tables=(<schema_name>.<table_name>)
 remap_tablespace=old:new
 </pre>
 Finally, recompile all invalid objects:
- exec utl_recomp.recomp_serial();
+<pre>
- or
+exec utl_recomp.recomp_serial();
- exec utl_recomp.recomp_parallel(4);
+or
- or
+exec utl_recomp.recomp_parallel(4);
- @?\rdbms\admin\utlrp.sql
+or
- or
+@?\rdbms\admin\utlrp.sql
- @?\rdbms\admin\utlprp.sql(4)
+or
 @?\rdbms\admin\utlprp.sql(4)
+</pre>

@@ 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]].<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>
   $ expdp schemas=JOE content=metadata_only

@@ Line 1: / Line 1: @@
 If the user does not yet exist on the destination database, just import it using the procedure outlined [[DataPump|here]].
+or if you are asked to create a user 'like' another user, use this:
 If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.<br />
 But, before dropping the schema, run [[Extract ddl with "dbms metadata.get ddl"#Get User DDL with related grants|this script]] to generate the correct DDL beforehand!<br />

@@ Line 14: / Line 14: @@
 and/or maybe one or more of the following:
- exclude=grants
+<pre>
- tables=(<schema name>.<table name>)
+exclude=grants
- remap_tablespace=old:new
+tables=(<schema name>.<table name>)
 Finally, recompile all invalid objects:
   exec utl_recomp.recomp_serial();

@@ Line 2: / Line 2: @@
 If however, the user(schema) already exists, it will have to be dropped and re-created before the data can be imported.<br />
-So before dropping the schema, run [[Extract ddl with "dbms metadata.get ddl":Get User DDL with related grants|this script]] to generate the correct DDL beforehand!
+So before dropping the schema, run [[Extract ddl with "dbms metadata.get ddl"#Get User DDL with related grants|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.