Schema Refresh in oracle

 Schema Refersh steps:
 =====================
 1. take expdport of a schema in source database(ctsp).

 expdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser.log schemas=raja

 Note: Dont give semicolon(;) at the end of the above command.

 2. move the dumpfile to target database(ctsd) using scp.if both the database running in same server this steps is not required.

scp ctsuser.dmp oracle@<Target server IP>:/u02/app/oracle

 3. create the new user in target database(if already existed drop and recreate)

 select username from dba_users;
 drop user raja cascade;
 create user raja identified by raja;

 4. Befor doing import(impdp) check the used space of a schema(user) in source database and freespace in the target database then
 target database tablespaces should be more than or equal to source database tablespaces.

 Source:

 select tablespace_name,sum(bytes/1024/1024) from dba_segments where owner='RAJA' group by tablespace_name;

 Target:

 select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;

5. Start the import(impdp) in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsuser.dmp logfile=ctsuser_impdp.log remap_schema=raja:raja

6. once completed compare the object counts between source and target databases.

 select object_type,count(*) from dba_objects where owner='RAJA' and status='VALID' group by object_type;
#!/bin/sh -x