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

From dbawiki
Jump to: navigation, search
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 />
 
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!
+
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 />
−
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.
+
After this DDL has been run and the results stored, the following command can be executed:
−
 
 
 
  drop user <schema name> cascade;
 
  drop user <schema name> cascade;
−
 
+
Now, recreate the schema with the above-mentioned DDL<br />
−
The schema should ideally be checked beforehand that it does not own objects like 'type's that may exist elsewhere...
+
Now the dumpfile (or a part thereof) can be imported to fill up the now empty schema:
−
 
 
−
Import the schema:
 
 
  impdp <usr>/<pwd> parfile=schema.par
 
  impdp <usr>/<pwd> parfile=schema.par
 
where schema.par would look like this:
 
where schema.par would look like this:
−
  schemas=<schema name>
+
directory=DP_IMPORT
 +
dumpfile=<db_name>_DP.DMP
 +
  schemas=(<schema name>)
 +
 
 +
and/or maybe one or more of the following:
 
  exclude=grants
 
  exclude=grants
 +
tables=(<schema name>.<table name>)
 +
remap_tablespace=old:new
 
Finally, recompile all invalid objects:
 
Finally, recompile all invalid objects:
 
  exec utl_recomp.recomp_serial();
 
  exec utl_recomp.recomp_serial();
Line 19: Line 22:
 
  exec utl_recomp.recomp_parallel(4);
 
  exec utl_recomp.recomp_parallel(4);
 
  or
 
  or
−
  @utlrp.sql
+
  @?\rdbms\admin\utlrp.sql
 
  or
 
  or
−
  @utlprp.sql(4)
+
  @?\rdbms\admin\utlprp.sql(4)
 +
 
 +
 
 +
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.
 +
 
 +
The schema should ideally be checked beforehand that it does not own objects like 'type's that may exist elsewhere...

Revision as of 15:34, 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.
But, before dropping the schema, run this script to generate the correct DDL beforehand!
After this DDL has been run and the results stored, the following command can be executed: 

drop user <schema name> cascade;

Now, recreate the schema with the above-mentioned DDL
Now the dumpfile (or a part thereof) can be imported to fill up the now empty schema: 

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

where schema.par would look like this: 

directory=DP_IMPORT
dumpfile=<db_name>_DP.DMP
schemas=(<schema name>)

and/or maybe one or more of the following: 

exclude=grants
tables=(<schema 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) 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. The schema should ideally be checked beforehand that it does not own objects like 'type's that may exist elsewhere...
Retrieved from "https://mailnest.com/dbawiki/index.php?title=(Re)create_user/schema_using_DataPump&oldid=132"