Blog

Oracle Database schema Refresh Activity ?


Solution:

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:

COL OWNER FORMAT A15

SELECT owner, tablespace_name,

round(Sum(bytes)/1024/1024,2) AS SIZE_in_MB

FROM dba_segments

WHERE owner like Upper(‘&User_Name’)

GROUP BY rollup(owner, tablespace_name);

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:

expdp \

system/abc123 \

directory = ADDP \

dumpfile = ADDB_full_dp.dmp \

logfile = ADDB_full_dp.log  \

schemas=REFRESH

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:

SET LINESIZE 200

SET PAGESIZE 200

COL CONSTRAINT_NAME FORMAT A20

select 

owner,

constraint_name,

constraint_type,

status

from 

dba_constraints

where

owner=’REFRESH’

and

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

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.

select 

owner,

constraint_name,

constraint_type,

status

from 

dba_constraints

where

owner=‘REFRESH’

and

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

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’;


At this point, schema has all the tables truncated.

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.

expdp \

system \

directory=ABC_SCHEMA \

dumpfile=Prod_REFRESH.dmp \

logfile=Prod_REFRESH.log \

schemas=REFRESH

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’;

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.