How to a Refresh Schema ?

1. Check the size of the Schema in Dev.

 SQL> select sum(bytes/1024/1024/1024) from dba_segments where owner='SLM';

SUM(BYTES/1024/1024/1024)
-------------------------
                .00012207

2. check the object count and status in Dev

SQL> select owner,object_type,count(*) from dba_objects where owner='SLM' group by owner,object_type;

OWNER                          OBJECT_TYPE           COUNT(*)

--------------------             -------------------               ----------

SLM                                     TABLE                              17

3. Check the available space in the file system

     $ df -h or $ df -g

4. create a directory OS Level and Database Level.

    $mkdir export                       -    os level

    sql>create directory export as '/db/DBATEST/i1/export';

5. check the directory created or not at database level of source database by using

   

              dba_directories

6. Export the Schema

  

   $ expdp dumpfile=rms12.dmp directory=export schemas=rms12 logfile=schemas_rms12.log

7. Copy the Dump file to target server

    $ scp dumpfilename username@target servername:/u01/backup/export

8. check the free tablespace size in target by using dba_freespace

9. If schema is already exists it will droped it.

   $ drop user username cascade;

10. import the Schema 

     $ impdp dumpfile=rms12.dmp directory=import schemas=rms12 

11. Finally check the object count and status

    

       SQL> select owner,object_type,count(*) from dba_objects where owner='SLM' group by                               owner,object_type;

               OWNER                          OBJECT_TYPE           COUNT(*)

               --------------------             -------------------               ----------

               SLM                                     TABLE                              17

                                                Thank You