Refresh schema

Refresh schema

Summary

Refresh test database schema(scott) with production database schema(scott).

1. export production database schema(scott) using datapump
2. copy dump file to test server
3. generate script to drop objects from test database
4. drop objects from test schema(scott) and purge from recyclebin
5. import data into test database schema(scott)
6. recompile invalid objects
7. match the objects count between production and test schema
8. collect statistics in test database schema (scott)
9. open for business

Steps:

1. export production database schema(scott) using datapump

userid="sys/manager as sysdba"

job_name=job1

FLASHBACK_TIME="TO_TIMESTAMP('04-10-2010 11:20:00', 'DD-MM-YYYY HH24:MI:SS')"

directory=dpdump

filesize=20000000000

SCHEMAS=SCOTT

dumpfile=expdp_DEMO_SCOTT.dmp

logfile=expdp_DEMO_SCOTT.log

2. copy dump file to test server

3. generate script to drop objects from test database

script: gen_drop_objects.sql

set pages 0  feedb off

accept inuser char prompt 'Enter Userid: '

spool run_drop_objects_&&inuser..out

select 'drop '||case when object_type='PACKAGE BODY' then 'PACKAGE' else object_type end||' '||owner||'.'||object_name||' ;'

from dba_objects

where owner like upper('&inuser')

/

prompt purge dba_recyclebin;

prompt /

spool off

Execute the above script from sqlplus

@gen_drop_objects.sql

Enter name of schema : scott

4. drop objects from test schema(scott) and purge from recyclebin

Execute the script from sqlplus

@run_drop_objects_scott.out

5. import data into test database schema(scott), using datapump

userid="sys/don as sysdba"

directory=dpdump

job_name=job1

version=10.2.0

EXCLUDE=statistics

schemas=SCOTT

dumpfile=expdp_DEMO_SCOTT.dmp

logfile=impdp_scott.log

6. recompile invalid objects

@?/rdbms/admin/utlrp

7. match the objects count between production and test schema, execute the following scripts in production and test database.

Script : count_objects.sql

select owner,

       object_type,

       count(*)

  from   sys.dba_objects

 where  owner like upper('&owner')

  group by rollup(owner,object_type);

      Execute the above script from sqlplus

Enter the schema name : scott

8. collect statistics in test database schema (scott), from sqlplus

  exec dbms_stats.gather_schema_stats(ownname =>'SCOTT',method_opt=>'FOR ALL INDEXES FOR ALL INDEXED COLUMNS SIZE 1',estimate_percent => 15,granularity=>'ALL',cascade=>TRUE);

9. Open for Business

 Hope this help. Regards Rupam