Table of Contents
Introduction
In this article, we'll learn how to perform the Schema Refresh in Oracle.
In my earlier articles, I demonstrated how to perform both backup-based and active duplicate database refreshes. If you haven’t read those yet, I recommend checking them out using the links provided.
What is Schema Refresh?
A Schema refresh is a process of updating the latest data, objects of the schema from PROD to NON-PROD database. So that application team can perform their testing in the latest data without impacting the actual schema which is in the PROD database.
Schema Refresh using Data pump utility
Now lets perform the Schema Refresh using Data pump utility using the below step by step process.
1) Check the object counts and Tablespace details in PROD and DEV database for schema refresh.
PROD Schema Object Counts and Tablespace details
Object Counts
============
a) select count(*) from dba_objects where owner='SOE';
COUNT(*)
----------
20
b) set lines 333 pages 111
col OWNER for a19
col OBJECT_TYPE for a21
select owner,object_type,count(*) from dba_objects where owner in ('SOE') group by owner,object_type order by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------- --------------------- ----------
SOE PACKAGE 1
SOE PACKAGE BODY 1
SOE SEQUENCE 5
SOE TABLE 11
SOE VIEW 2
Tablespace Details
================
c) set lines 999 pages 999
col username for a30
select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='SOE';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
SOE OPEN SOE TEMP
DEV Schema Object Counts and Tablespace details
a) select count(*) from dba_objects where owner='SOE';
COUNT(*)
----------
5
b) set lines 333 pages 111
col OWNER for a19
col OBJECT_TYPE for a21
select owner,object_type,count(*) from dba_objects where owner in ('SOE') group by owner,object_type order by owner,object_type;
OWNER OBJECT_TYPE COUNT(*)
------------------- --------------------- ----------
SOE INDEX 3
SOE TABLE 2
Tablespace Details
================
c) set lines 999 pages 999
col username for a30
select username,account_status,default_tablespace,temporary_tablespace from dba_users where username='SOE';
USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ -------------------------------- ------------------------------ ------------------------------
SOE OPEN SOE TEMP
Step 2) We need to create the respective directory where we will perform the export and import jobs for both the PROD and DEV database side for schema refresh activity.
PROD Side Creation
set lines 200 pages 200
col owner for a10
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a60
select * from dba_directories;
Command : - create or replace directory SOE_EXP as '/u01/app/oracle/SOE';
Output
SQL> create or replace directory SOE_EXP as '/u01/app/oracle/SOE';
Directory created.
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ------------------------------------------------------------
SYS SOE_EXP /u01/app/oracle/SOE
DEV Side Creation
Output
SQL> create or replace directory SOE_EXP as '/u01/app/oracle/SOE';
Directory created.
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- ------------------------------ ------------------------------------------------------------
SYS SOE_EXP /u01/app/oracle/SOE
Step 3) Now we need to take the output of USER DDL, PROFILE and PASSWORD of SOE schema refresh in DEV side to rollback in case of any issues.
Script to find out the user DDL on DEV Side
set verify off
set lines 180
col text for a180
set pages 0
set long 100000
set feedback off
set trimspool on
accept _username prompt "Please enter the username : "
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true)
spool &_username
prompt -- User Creation Script : "
select dbms_metadata.get_ddl('USER',upper('&_username')) text from dual
where 0 != ( select count(*) from dba_users where username = upper('&_username') ) ;
prompt -- OBJECT GRANTS : "
select dbms_metadata.get_granted_ddl('OBJECT_GRANT',upper('&_username')) text from dual
where 0 != ( select count(*) from dba_tab_privs where grantee = upper('&_username') )
or 0 != ( select count(*) from dba_col_privs where grantee = upper('&_username') ) ;
prompt -- SYSTEM GRANTS : "
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT',upper('&_username')) text from dual
where 0 != ( select count(*) from dba_SYS_privs where grantee = upper( '&_username') ) ;
prompt -- ROLE GRANTS : "
select dbms_metadata.get_granted_ddl('ROLE_GRANT',upper('&_username')) text from dual
where 0 != ( select count(*) from dba_role_privs where grantee = upper('&_username') ) ;
prompt -- TABLESPACE QUOTA "
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA',upper('&_username')) text from dual
where 0 != ( select count(*) from dba_ts_quotas where username = upper('&_username') ) ;
spool off
set feedback on
Output
USER DDL
=========
SQL> @user.sql
Please enter the username : SOE
-- User Creation Script : "
CREATE USER "SOE" IDENTIFIED BY VALUES 'S:96EF4300D725A54EDACC3F8CF5CB8EE2714BA094F6E1F866298E2C3523D7;T:6646A17D24588C50C5CC35420CA7B2CF2348464F768AF4C2B0B0D91011608DCBAE6DF3B353D7C3E10AF477DB22D50A0DC2FE3BE10B2DFC9223E'
DEFAULT TABLESPACE "SOE"
TEMPORARY TABLESPACE "TEMP";
-- OBJECT GRANTS : "
-- SYSTEM GRANTS : "
-- ROLE GRANTS : "
GRANT "CONNECT" TO "SOE";
GRANT "RESOURCE" TO "SOE";
-- TABLESPACE QUOTA "
DECLARE
TEMP_COUNT NUMBER;
SQLSTR VARCHAR2(200);
BEGIN
SQLSTR := 'ALTER USER "SOE" QUOTA UNLIMITED ON "SOE"';
EXECUTE IMMEDIATE SQLSTR;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -30041 THEN
SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES
WHERE TABLESPACE_NAME = ''SOE'' AND CONTENTS = ''TEMPORARY''';
EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;
IF TEMP_COUNT = 1 THEN RETURN;
ELSE RAISE;
END IF;
ELSE
RAISE;
END IF;
END;
/
Profile Output
============
set lines 200
set pages 200
SET LINES 200 PAGES 500
COL USERNAME FOR A30
COL PROFILE FOR A30
COL ACCOUNT_STATUS FOR A20
SELECT USERNAME,PROFILE,ACCOUNT_STATUS,CREATED,LOCK_DATE,EXPIRY_DATE FROM DBA_USERS WHERE USERNAME='SOE';
USERNAME PROFILE ACCOUNT_STATUS CREATED LOCK_DATE EXPIRY_DA
------------------------------ ------------------------------ -------------------- --------- --------- ---------
SOE DEFAULT OPEN 11-NOV-25 10-MAY-26
Step 4) We can take a export backup of the SOE schema refresh in the DEV side to rollback in case of any issues.
Note: – In case you want to proceed directly without taking export you can go head, but it is recommend to take the backup so that in case if user want their schema back with all the details so that you have valid export backup which you can restore it back.
a) cd /u01/app/oracle/SOE
b) Parfile Name: - SOE_backup_Dev.par
vi SOE_backup_Dev.par
userid='/ as sysdba'
DIRECTORY=SOE_EXP
schemas=SOE
dumpfile=SOE_DEV_%U.dmp
logfile=SOE_DEV.log
parallel=4
c) chmod 755 SOE_backup_Dev.par
d) nohup expdp parfile=SOE_backup_Dev.par &
Output
[oracle@practice SOE]$ tail -100f nohup.out
Export: Release 19.0.0.0.0 - Production on Tue Nov 11 16:27:56 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA parfile=SOE_backup_Dev.par
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SOE"."DEPARTMENTS" 5.539 KB 3 rows
. . exported "SOE"."EMPLOYEES" 7.382 KB 3 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/SOE/SOE_DEV_01.dmp
/u01/app/oracle/SOE/SOE_DEV_02.dmp
/u01/app/oracle/SOE/SOE_DEV_03.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 11 16:28:22 2025 elapsed 0 00:00:22
[1]+ Done nohup expdp parfile=SOE_backup_Dev.par
[oracle@practice SOE]$ ls -lrt
total 396
-rwxr-xr-x 1 oracle oinstall 106 Nov 11 16:26 SOE_backup_Dev.par
-rw-r--r-- 1 oracle oinstall 1926 Nov 11 16:28 SOE_DEV.log
-rw-r----- 1 oracle oinstall 12288 Nov 11 16:28 SOE_DEV_03.dmp
-rw-r----- 1 oracle oinstall 32768 Nov 11 16:28 SOE_DEV_02.dmp
-rw-r----- 1 oracle oinstall 348160 Nov 11 16:28 SOE_DEV_01.dmp
-rw------- 1 oracle oinstall 1550 Nov 11 16:28 nohup.outStep 5) Drop SOE schema in DEV Side before starting the schema refresh activity.
Command : - drop user SOE cascade;
Output
SQL> drop user SOE cascade;
User dropped.
Step 6) We can take a export backup of the SOE schema in the PROD side for schema refresh.
a) cd /u01/app/oracle/SOE
b) Parfile Name: - SOE_backup_PROD.par
vi SOE_backup_PROD.par
userid='/ as sysdba'
DIRECTORY=SOE_EXP
schemas=SOE
dumpfile=SOE_PROD_%U.dmp
logfile=SOE_PROD.log
parallel=4
c) chmod 755 SOE_backup_PROD.par
d) nohup expdp parfile=SOE_backup_PROD.par &
Output
[oracle@linux2 ] /u01/app/oracle/SOE$ tail -100f nohup.out
Export: Release 19.0.0.0.0 - Production on Tue Nov 11 16:33:53 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA parfile=SOE_backup_PROD.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/VIEW/VIEW
. . exported "SOE"."ORDERS" 129.0 MB 1429790 rows
. . exported "SOE"."CUSTOMERS" 109.4 MB 1000000 rows
. . exported "SOE"."ADDRESSES" 110.4 MB 1500000 rows
. . exported "SOE"."ORDER_ITEMS" 228.4 MB 4289944 rows
. . exported "SOE"."PRODUCT_DESCRIPTIONS" 221.3 KB 1000 rows
. . exported "SOE"."PRODUCT_INFORMATION" 185.6 KB 1000 rows
. . exported "SOE"."WAREHOUSES" 35.38 KB 1000 rows
. . exported "SOE"."CARD_DETAILS" 63.87 MB 1500000 rows
. . exported "SOE"."ORDERENTRY_METADATA" 5.617 KB 4 rows
. . exported "SOE"."LOGON" 51.24 MB 2382984 rows
. . exported "SOE"."INVENTORIES" 15.22 MB 899034 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/SOE/SOE_PROD_01.dmp
/u01/app/oracle/SOE/SOE_PROD_02.dmp
/u01/app/oracle/SOE/SOE_PROD_03.dmp
/u01/app/oracle/SOE/SOE_PROD_04.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Nov 11 16:34:24 2025 elapsed 0 00:00:28
[1]+ Done nohup expdp parfile=SOE_backup_PROD.par
[oracle@linux2] /u01/app/oracle/SOE$ ls -lrt
total 725752
-rwxr-xr-x 1 oracle oinstall 109 Nov 11 16:33 SOE_backup_PROD.par
-rw-r----- 1 oracle oinstall 202461184 Nov 11 16:34 SOE_PROD_03.dmp
-rw-r----- 1 oracle oinstall 131768320 Nov 11 16:34 SOE_PROD_02.dmp
-rw-r----- 1 oracle oinstall 168869888 Nov 11 16:34 SOE_PROD_01.dmp
-rw-r----- 1 oracle oinstall 240058368 Nov 11 16:34 SOE_PROD_04.dmp
-rw-r--r-- 1 oracle oinstall 2965 Nov 11 16:34 SOE_PROD.log
-rw------- 1 oracle oinstall 2587 Nov 11 16:34 nohup.out
Step 7) In this step of the schema refresh activity we need to do the SCP and transfer all the dump files from PROD to DEV side for import operation of the SOE schema.
Output
oracle@linux2 ORAP0397 /u01/app/oracle/SOE$ scp *.dmp oracle@practice:/u01/app/oracle/SOE/
oracle@practice's password:
SOE_PROD_01.dmp 100% 161MB 40.2MB/s 00:04
SOE_PROD_02.dmp 100% 126MB 72.2MB/s 00:01
SOE_PROD_03.dmp 100% 193MB 64.4MB/s 00:03
SOE_PROD_04.dmp
Note : - Check on the DEV side whether all the dump files are present and change the permission of the dmp files to 755 on DEV side so that during import operation it will not through any permission issue.
cd /u01/app/oracle/SOE
[oracle@practice SOE]$ ls -lrt
-rwxr-xr-x 1 oracle oinstall 168869888 Nov 11 16:39 SOE_PROD_01.dmp
-rwxr-xr-x 1 oracle oinstall 131768320 Nov 11 16:39 SOE_PROD_02.dmp
-rwxr-xr-x 1 oracle oinstall 202461184 Nov 11 16:39 SOE_PROD_03.dmp
-rwxr-xr-x 1 oracle oinstall 240058368 Nov 11 16:39 SOE_PROD_04.dmp
Step 8) Import of PROD SOE schema in DEV database.
Prepare the import parameter file for the schema refresh.
a) cd /u01/app/oracle/SOE
b) Parfile name: - DEV_imp.par
vi DEV_imp.par
userid='/ as sysdba'
DIRECTORY=SOE_EXP
schemas=SOE
dumpfile=SOE_PROD_%U.dmp ------------------------> Make sure you will provide the dump file same which you have taken at the PROD side
logfile=DEV_imp.log
parallel=4
c) chmod 755 DEV_imp.par
d) nohup impdp parfile=DEV_imp.par &
Note: - If in your case Schema and the tablespace name is different between PROD and DEV side then you also need to add 2 more parameter call remap_schema and remap_tablespace. In my case both Schema and Tablespace are same which I have already checked in step 1), so I can proceed with schema refresh activity without these 2 parameters.
For eg)
Remap_schema=SOE_PROD:SOE_DEV
Remap_Tablespace=SOE_PROD:SOE_DEV
Output of the import
===================
[oracle@practice SOE]$ tail -100f nohup.out
Import: Release 19.0.0.0.0 - Production on Tue Nov 11 16:56:16 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": /******** AS SYSDBA parfile=DEV_imp.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SOE"."ADDRESSES" 110.4 MB 1500000 rows
. . imported "SOE"."CUSTOMERS" 109.4 MB 1000000 rows
. . imported "SOE"."ORDERS" 129.0 MB 1429790 rows
. . imported "SOE"."LOGON" 51.24 MB 2382984 rows
. . imported "SOE"."PRODUCT_DESCRIPTIONS" 221.3 KB 1000 rows
. . imported "SOE"."PRODUCT_INFORMATION" 185.6 KB 1000 rows
. . imported "SOE"."WAREHOUSES" 35.38 KB 1000 rows
. . imported "SOE"."ORDERENTRY_METADATA" 5.617 KB 4 rows
. . imported "SOE"."CARD_DETAILS" 63.87 MB 1500000 rows
. . imported "SOE"."ORDER_ITEMS" 228.4 MB 4289944 rows
. . imported "SOE"."INVENTORIES" 15.22 MB 899034 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39082: Object type PACKAGE BODY:"SOE"."ORDERENTRY" created with compilation warnings
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Tue Nov 11 16:57:43 2025 elapsed 0 00:01:25
==> Here we are getting one warning for PACKAGE BODY:"SOE"."ORDERENTRY" which we can identify and resolve it.
Solution: -
a) Check the error using below command.
Command : - SHOW ERRORS PACKAGE BODY SOE.ORDERENTRY;
Errors for PACKAGE BODY SOE.ORDERENTRY:
LINE/COL ERROR
-------- -----------------------------------------------------------------
63/7 PL/SQL: SQL Statement ignored
64/36 PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got TIME
WITH TIME ZONE
89/11 PL/SQL: Statement ignored
89/11 PLS-00201: identifier 'DBMS_LOCK' must be declared
92/11 PL/SQL: Statement ignored
92/11 PLS-00201: identifier 'DBMS_LOCK' must be declared
b) Provide the necessary grant and recompile the package body again for SOE.ORDERENTRY
SQL> GRANT EXECUTE ON DBMS_LOCK TO SOE;
Grant succeeded.
SQL> ALTER PACKAGE SOE.ORDERENTRY COMPILE BODY;
Package body altered.
c) Check the error again for SOE.ORDERENTRY.
SHOW ERRORS PACKAGE BODY SOE.ORDERENTRY;
No errors.
Step 9) We need to crosscheck the counts of object of schema between PROD and DEV database after schema refresh.
PROD Side output
================
DEV Side Output
===============
Step 10) Check the INVALID objects counts in DEV side after schema refresh activity.
Command : - SELECT owner ,status,object_type ,object_name FROM dba_objects WHERE status != 'VALID' and owner='SOE' order by owner, object_type ;
Output
SQL> SELECT owner ,status,object_type ,object_name FROM dba_objects WHERE status != 'VALID' and owner='SOE' order by owner, object_type ;
no rows selected
Step 11) We need to run the UTLRP script after completion of import on DEV side.
Output
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2025-11-11 17:11:04
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2025-11-11 17:11:08
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
Step 12) We need to gather the stats as well for that schema in Dev side after schema refresh activity.
Command : - EXEC DBMS_STATS.gather_schema_stats('SOE', estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE,degree=>DBMS_STATS.AUTO_DEGREE);
Output
SQL> EXEC DBMS_STATS.gather_schema_stats('SOE', estimate_percent => dbms_stats.auto_sample_size, cascade => TRUE,degree=>DBMS_STATS.AUTO_DEGREE);
PL/SQL procedure successfully completed.
Step 13) Grant all the privilege to SOE schema as per user DDL in Step 3).
Conclusion
Finally!!
We have successfully saw the steps of How to perform Schema Refresh using data pump utility in Oracle.
Note: – If you want to practice this whole activity in your home lab, then you'll need a platform to perform the installation. To set that up, you first need to download and install Oracle VirtualBox, followed by the operating system, the Oracle binary software, and finally, create the database.
