Steps for sehema refresh

Schema refresh in oracle 9i
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.

1. SELECT object_type,count(*) from dba_objects where owner=’SHTEST’ group by object_type;
2. Verify total no of objects from above query.
3. write a dynamic query as below
4. select ‘grant ‘ || privilege ||’ to sh;’ from session_privs;
5. select ‘grant ‘ || role ||’ to sh;’ from session_roles;
6. query the default tablespace and size
7. 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
steps to drrop and recreate schema
Drop the SH schema

1. Create the SH schema with the default tablespace and allocate quota on that tablespace.
2. Now run the roles and privileges spooled scripts.
3. Connect the SH and verify the tablespace, roles and privileges.
4. then start importing

  Importing The ‘SH’ schema
 Imp ‘usernmae/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’);
execdbms_utility.analyze_schema(‘SH’,'ESTIMATE’,ESTIMATE_PERCENT=>20);
 Now connect the SH user and check for the import data.
 Schema refresh by dropping objects and truncating objects
 Export the ‘sh’ schema
 Take the schema full export as show above
 Drop all the objects in ‘SH’ schema
To drop the all the objects in the Schema
 Connect the schema
Spool the output
 SQL>set head off
SQL>spool drop_tables.sql
SQL>select ‘drop table ‘||table_name||’ cascade constraints purge;’ from user_tables;
SQL>spool off
 SQL>set head off
SQL>spool drop_other_objects.sql
SQL>select ‘drop ‘||object_type||’ ‘||object_name||’;’ from user_objects;
SQL>spool off
Now run the script all the objects will be dropped,
 Importing THE ‘SH’ schema
Imp ‘usernmae/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’);
execdbms_utility.analyze_schema(‘SH’,'ESTIMATE’,ESTIMATE_PERCENT=>20);
 Now connect the SH user and check for the import data.
To enable constraints use the query below
SELECT ‘ALTER TABLE ‘||TABLE_NAME||’ENABLE CONSTRAINT ‘||CONSTRAINT_NAME||’;'FROM USER_CONSTRAINTS
WHERE STATUS=’DISABLED’;
 Truncate all the objects in ‘SH’ schema
 To truncate the all the objects in the Schema
 Connect the schema
Spool the output
 SQL>set head off 
SQL>spool truncate_tables.sql
SQL>select ‘truncate table ‘||table_name from user_tables;
SQL>spool off
 SQL>set head off 
SQL>spool truncate_other_objects.sql
SQL>select ‘truncate ‘||object_type||’ ‘||object_name||’;’ from user_objects;
SQL>spool off
Now run the script all the objects will be truncated.
Disabiling the reference constraints
 If there is any constraint violation while truncating use the below query to find reference key constraints and disable them. Spool the output of below query and run the script.
 Select constraint_name,constraint_type,table_name FROM ALL_CONSTRAINTS 
 where constraint_type=’R’
 and r_constraint_name in (select constraint_name from all_constraints
 where  table_name=’TABLE_NAME’)
 Importing THE ‘SH’ schema
Imp ‘usernmae/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);
Now connect the SH user and check for the import data.
 Schema refresh in oracle 10g
Here we can use Datapump
 Exporting the SH schema through Datapump
 expdp ‘username/password’ dumpfile=sh_exp.dmp directory=data_pump_dir schemas=sh
Dropping  the ‘SH’ user
  Query the default tablespace and verify the space in the tablespace and drop  the user.
 SQL>Drop user SH cascade;
Importing the SH schema through datapump
impdp ‘username/password’ dumpfile=sh_exp.dmp directory=data_pump_dir schemas=sh
 If you are importing to different schema use remap_schema option.
 Check for the imported objects and compile the invalid objects.