Refresh Oracle Schema by exp/imp

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

1. 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.
2. 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;
3. query the default tablespace and size
   select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner=’SH’ group by tablespace_name;
4. Export the ‘sh’ schema
   exp ‘usernmae/password file=’/location/sh_bkp.dmp’ log=’/location/sh_exp.log’ owner=’SH’ direct=y
5. Create the SH schema with the default tablespace and allocate quota on that tablespace on target database.
6. Now run the roles and privileges spooled scripts.
7. Connect the SH and verify the tablespace, roles and privileges then start importing
8. 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;
9. 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