Schema refresh steps from Test to Pre-production environment

Scenario 1:
To copy the MNPHIST schema (only the structure not the data) from SIT1  to Pre-production environment:


Source: HOST = Test1
Target: HOST = preTest 

SQL> select name,instance_name,open_mode,to_char(STARTUP_TIME,'dd/mm/yy hh24:mi:ss'),STATUS ,HOST_NAME from v$database,v$instance;
NAME      INSTANCE_NAME    OPEN_MODE            TO_CHAR(STARTUP_T STATUS    HOST_NAME
--------- ---------------- -------------------- ----------------- --------------------
MVMNPTS1  mvmnpts1_2       READ WRITE           30/06/12 00:09:01 OPEN    prser032

SQL>  SELECT OBJECT_TYPE,COUNT(*) FROM dba_objects WHERE OWNER='MNPHIST' GROUP BY OBJECT_TYPE;

OBJECT_TYPE           COUNT(*)
------------------- ----------
PROCEDURE                   42
INDEX                      124
TABLE                       62


srvctl status database -d MVMNPT -v -f

SELECT Sum(bytes)/1024 AS total_size_GB FROM dba_segments WHERE owner ='GPCOMP_MT02';

SELECT Sum(bytes)/1024/1024/1024 AS total_size_GB FROM dba_segments WHERE owner like 'MNPHIST';

SELECT OBJECT_TYPE,COUNT(*) FROM dba_objects WHERE OWNER='MNPHIST' GROUP BY OBJECT_TYPE;
NAME      INSTANCE_NAME    OPEN_MODE            TO_CHAR(STARTUP_T STATUS    HOST_NAME
--------- ---------------- -------------------- ----------------- ------------
MNPT      MNPT_2           READ WRITE           18/07/12 14:58:39 OPEN    prser035


SQL> SELECT * FROM dba_directories;
OWNER                          DIRECTORY_NAME    DIRECTORY_PATH
------------------------------ ------------------------------
SYS                            MNP_EXP_DUMP_PRE    /export_MNP/preprod === =here dump file is there
SYS                            MNP_EXP_DUMP_PROD    /export_MNP/prod
SYS                            XMLDIR    /u01/app/oracle/product/11.2.0/MNPT/rdbms/xml
SYS                            EXP_BKP_MNPT2    /dump/exp_bkp_MNPT2
SYS                            DATA_PUMP_DIR    /u01/app/oracle/product/11.2.0/MNPT/rdbms/log/
SYS                            ORACLE_OCM_CONFIG_DIR    /u01/app/oracle/product/11.2.0/MNPT/ccr/state

expdp \'/ as sysdba \' directory=DATA_PUMP_DIR dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_expdp_source.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp 

Ftp the dump file from source to the target

impdp \'/ as sysdba \' directory=DATA_PUMP_DIR dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_expdp_source.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp

Sytax to run Export and Import in background  if its too big:
nohup expdp \'/ as sysdba \' directory=MNP_EXP_DUMP_PRE dumpfile=MNPHIST_expdp_target.dmp logfile=MNPHIST_expdp_target.log schemas=MNPHIST CONSISTENT=Y CONTENT=metadata_only job_name=MNPHISTexpdp &

nohup impdp \'/ as sysdba \' directory=MNP_EXP_DUMP_PRE dumpfile=MNPHIST_expdp_source.dmp logfile=MNPHIST_impdp_source.log schemas=MNPHIST job_name=MNPHISTimpdp &

@?/rdbms/admin/utlrp.sql: 
ORACLE_HOME/rdbms/admin/utlrp.sql to compile the invalid objects
Count the objects:
select object_type,status,count(*) from dba_objects where owner='MNPHIST' group by object_type,status;

Enjoy the work and do like me if you like this article!!!