Schema Refresh

No.

Description

Command / Screenshot

1

Find the size of the schema

• . oraenv → BATCH46

• sqlplus / as sysdba

• To find the size of the schema, execute the following:

2

Find the directory where schema level export can be taken using data pump

set linesize 200    

set pagesize 200    

column directory_path format a20   

Select directory_name, directory_path FROM dba_directories order by 1;  

Note: I will use ADDP  directory to take an export of the entire refresh schema

3.

Take a backup of the schema using Data Pump

• Exit of our sqlplus 

• To start the export using Data Pump, execute the following:

4

Verify the dumpfile are created

ls -ltrh /u18/BATCH46/ADDP

5

Find the status of the constraints.

• . oraenv → BATCH46

• sqlplus / as sysdba

• To check the status of all FOREIGN KEY and CHECK constraints, execute the following:

6

Disable all constraints

BEGIN

  FOR a IN

(

select 

a.owner, 

a.table_name, 

a.constraint_name

from 

all_constraints a, 

all_constraints b

where 

a.constraint_type = ‘R’

and 

a.r_constraint_name = b.constraint_name

and 

a.r_owner = b.owner

and 

b.owner=’REFRESH’

union

SELECT 

a.owner, 

a.table_name, 

a.constraint_name

FROM 

all_constraints a

WHERE 

a.constraint_type = ‘C’

and 

a.owner=’REFRESH’

)

 LOOP

dbms_utility.exec_ddl_statement(‘alter table ‘ || a.owner || ‘.’ || a.table_name || ‘ DISABLE constraint ‘ || a.constraint_name);

  END LOOP;

END;

/

7

Check the status of the constraints again

• Execute the following and as you can see all constraints are disabled.

8

Check tables and row count the schema

select

owner,

table_name,

num_rows

from

dba_tables

where

owner=‘REFRESH’;

8

Now truncate all the tables in the schema

BEGIN

  FOR a IN

(

select owner, table_name from dba_tables where owner=’REFRESH’

)

 LOOP

dbms_utility.exec_ddl_statement(‘Truncate table ‘ || a.owner || ‘.’ || a.table_name);

  END LOOP;

END;

/

9

Gather schema stats

exec dbms_stats.gather_schema_stats (‘REFRESH’, dbms_stats.auto_sample_size);

10.

Check table and row count again in schema. All tables should have zero rows.

select

owner,

table_name,

num_rows

from

dba_tables

where

owner=‘REFRESH’;

No.

Description

Command / Screenshot

11

Take an export on Production.

1. Putty to production server

2. Su  – oracle

3. . oraenv → ProdDB

4. Execute the following command on Prod to start export using Data Pump. 

Please note that you may have to change the “directory=ABC_SCHEMA” to the directory which is created in ProdDB.

12

Send the .dump file to destination server where you have to import data only

cd /u18/TESTDB/APP

scp *Prod_REFRESH* DestinationHOST:/u18/TESTDB/APP/.

13

Log back into the Destination SErver

1. Putty to Destination Server

2. Su  – oracle

3. . oraenv → TESTDB

14

Lets say if the schema will need to be refreshed from the another a prod .dmp file without recreating the tables, then execute the following:

impdp \

system \

directory=ADDP \

dumpfile=ADDB_full_dp.dmp  \

logfile=Prod_REFRESH_IMP.log \

schemas=REFRESH \

content=DATA_ONLY

15

Enable the constraints

BEGIN

  FOR a IN

(

select 

a.owner, 

a.table_name, 

a.constraint_name

from 

all_constraints a, 

all_constraints b

where 

a.constraint_type = ‘R’

and 

a.r_constraint_name = b.constraint_name

and 

a.r_owner = b.owner

and 

b.owner=’REFRESH’

union

SELECT 

a.owner, 

a.table_name, 

a.constraint_name

FROM 

all_constraints a

WHERE 

a.constraint_type = ‘C’

and 

a.owner=’REFRESH’

)

 LOOP

dbms_utility.exec_ddl_statement(‘alter table ‘ || a.owner || ‘.’ || a.table_name || ‘ ENABLE constraint ‘ || a.constraint_name);

  END LOOP;

END;

/

16

Verify constraints are enabled.

select 

owner,

constraint_name,

constraint_type,

status

from 

dba_constraints

where

owner=‘REFRESH’

and

constraint_type IN (‘R’,’C’);

17

Collect the fresh statistics on the schema

exec dbms_stats.gather_schema_stats (‘REFRESH’, dbms_stats.auto_sample_size);

18

Verify statistics are collected.

select

owner,

table_name,

num_rows

from

dba_tables

where

owner=‘REFRESH’;

(OR)

set linesize 200

set pagesize 200

select

owner,

table_name,

num_rows,

to_char(last_analyzed, ‘HH24:MI:SS MM-DD-YYYY’) as Last_Analyzed

from

dba_tables

where

owner=’REFRESH’;