Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 1 May 2012

Schema Refreshing in Oracle 9i

OS: Windows 2003 and Database: Oracle 9i R2

Tools used: Import/Export

Schema Name: HRMS

Scenario: We have two databases in different servers, one is our production database and the other is the test database. We have created test database as a clone of production database earlier (Same structure and instance name). Now we need to refresh my one of schema (HRMS) of test database. Before going to start the actual process I just try to clear the concept of difference between Cloning and Refreshing the database.
Cloning VS Refreshing:
Cloning a database means having exact image of production database including ORACLE-HOME where as refreshing database something like applying changes or update of production database to the database where the database already cloned or duplicated. That is nothing but duplicating with the duplicate database with newly backup. This process does not include ORACLE-HOME.

Spool the output of roles and privileges using these query or you ca use TOAD to generate the script. For More details to refresh a schema using the Toad please follow the below link: Restore or Refresh Schema using Import/Export with Toad
SELECT object_type,count(*) from dba_objects where owner='HRMS' group by object_type;
select 'Grant' || privilege ||'  to HRMS' from session_privs;
select 'Grant'  || role ||'  to HRMS'  from session_roles;
Query to find Default tablespace and size of that Schema:
select tablespace_name,sum(bytes/1024/1024) "Size in MB" from dba_segments where owner='HRMS' group by tablespace_name;
Export the ‘HRMS’ schema
exp 'system/oracle1 file='d:\hrms\hrms_bkp.dmp' log='d:\hrms\hrms_exp.log' owner='HRMS' direct=y

Steps to drop and recreate schema
1.      Drop the HRMS schema
2.      Create the HRMS schema with the default tablespace and allocate quota.
3.      Now run the roles and privileges spooled scripts.
4.      Connect the HRMS and verify the tablespace, roles and privileges.
5.      then start importing

Drop all the objects in ‘HRMS’ schema
To drop the all the objects in the Schema connect the schema and Spool the output then run the script for drop the schema.
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

Importing The ‘HRMS’ schema
Imp ‘system/oracle’ file=’d:\hrms\hrms_bkp.dmp’ log=’d:\hrms\hrms_imp.log’
Fromuser=’HRMS’ touser=’HRMS’

SELECT object_type,count(*) from dba_objects where owner= 'HRMS' group by object_type; 
Compiling and analyzing HRMS Schema

exec dbms_utility.compile_schema(‘HRMS’);
Now connect the HRMS user and check for the import data.
To enable constraints use the query below
Truncate all the objects in ‘HRMS’ schema
To truncate the all the objects in the Schema connect the schema and 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') ;


Post a Comment