Refresh Oracle Schema by exp/imp

tvrwmcelsw

　　Now we are going to refresh SH schema.
Steps for schema refresh – before exporting

• Spool the output of roles and privileges assigned to the user .use the query below to view the role s and privileges and spool the out as .sql file.
  SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type;
  Verify total no of objects from above query.
  
• write a dynamic query as below to generate privillege granting sql
  select ‘grant ‘ || privilege ||’ to sh;’ from session_privs;
  select ‘grant ‘ || role ||’ to sh;’ from session_roles;
  
• query the default tablespace and size
  select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner=’SH’ group by tablespace_name;
  
• Export the ‘sh’ schema
  exp ‘usernmae/password file=’/location/sh_bkp.dmp’ log=’/location/sh_exp.log’ owner=’SH’ direct=y
  
• Create the SH schema with the default tablespace and allocate quota on that tablespace on target database.
  
• Now run the roles and privileges spooled scripts.
  
• Connect the SH and verify the tablespace, roles and privileges then start importing
  
• Importing The ‘SH’ schema
  imp ‘username/password’ file=’/location/sh_bkp.dmp’ log=’/location/sh_imp.log’ Fromuser=’SH’ touser=’SH’
  SQL> SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type;
  
• Compiling and analyzing SH Schema
  exec dbms_utility.compile_schema(‘SH’);
  exec dbms_utility.analyze_schema(‘SH’,'ESTIMATE’,ESTIMATE_PERCENT=>20);
  

　　参考至:http://vinu3012dba.blogspot.com/2010/10/schema-refresh-in-oracle-9i.html
　　如有错误，欢迎指正
　　邮箱:czmcj@163.com