Cloning schema using EXPDP and IMPDP without rows

Schema Refresh :

Schema refresh is one of the major or regular activity  we do in most of the environments.    Most cases where we use schema refresh for

  • Bug on PROD
  • for a test case with production data
  • to create a duplicate environment etc

We got a request to do a schema refresh without rows on 12.2.0 oracle database.

It was  simple to do expdp.

Create appropriate directory

$ sqlplus / as sysdba

SQL> create directory APP_DIR as '/backup/EXP/MXCD';

SQL> grant read,write on directory APP_DIR to public;

Then execute the expdp command.

 expdp system/***** directory=APP_DIR dumpfile=SOURCE_SCHEMA.dmp logfile=SOURCE_SCHEMA.log SCHEMAS=APP_USR CONTENT=METADATA_ONLY


Export: Release 12.2.0.1.0 - Production on Tue Nov 27 06:59:46 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_03": system/***** directory=APP_DIR dumpfile=SOURCE_SCHEMA.dmp logfile=SOURCE_SCHEMA.log SCHEMAS=APP_USR CONTENT=METADATA_ONLY

Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTIC
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_03 is:
/backup/EXP/MXCD/SCOURCE_SCHEMA.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_03" successfully completed at Tue Nov 27 07:25:05 2018 elapsed 0 00:25:19

Before Importing into the target schema ,

Make sure you create the TYPES , if exists

CREATE OR REPLACE TYPE "DSTS_TYPE" AS OBJECT( NAME VARCHAR2(30), VALUE VARCHAR2(250) );
/

CREATE OR REPLACE TYPE "VARRAY_DSTYPE" AS VARRAY(100) OF NUMBER(12, 4);
/

CREATE OR REPLACE TYPE "MVTSTYE" AS VARRAY(16) OF VARRAY_DSTYPE;
/

Now you can import the schema .

 
impdp system/****** directory=APP_DIR dumpfile=SOURCE_SCHEMA.dmp logfile=imp_SOURCE_SCHEMAlog REMAP_SCHEMA=APP_USER:APP_USER_CLONE

 

After Import ,

To verify use the below Query.

select owner,object_type,count(*) from dba_objects where owner like 'SOURCE_SCHEMA' group by object_type,owner;

 

TARGET_SCHEMA FUNCTION 7
TARGET_SCHEMA INDEX 3089
TARGET_SCHEMA JOB 1
TARGET_SCHEMA LOB 51
TARGET_SCHEMA MATERIALIZED VIEW 42
TARGET_SCHEMA PACKAGE 1
TARGET_SCHEMA PACKAGE BODY 1
TARGET_SCHEMA PROCEDURE 11
TARGET_SCHEMA SEQUENCE 581
TARGET_SCHEMA TABLE 729
TARGET_SCHEMA TYPE 3
TARGET_SCHEMA VIEW 216

 

select owner,object_type,count(*) from dba_objects where owner like 'TARGET_SCHEMA' group by object_type,owner;

 

TARGET_SCHEMA FUNCTION 8
TARGET_SCHEMA INDEX 3087
TARGET_SCHEMA LOB 51
TARGET_SCHEMA MATERIALIZED VIEW 36
TARGET_SCHEMA PACKAGE 1
TARGET_SCHEMA PACKAGE BODY 1
TARGET_SCHEMA PROCEDURE 17
TARGET_SCHEMA SEQUENCE 582
TARGET_SCHEMA TABLE 729
TARGET_SCHEMA TYPE 3
TARGET_SCHEMA VIEW 216

You can see some differences in the objects show below list after comparing .

TARGET_SCHEMA FUNCTION 7 TARGET_SCHEMA FUNCTION 8
TARGET_SCHEMA INDEX 3089 TARGET_SCHEMA INDEX 3087
TARGET_SCHEMA JOB 1
TARGET_SCHEMA LOB 51 TARGET_SCHEMA LOB 51
TARGET_SCHEMA MATERIALIZED VIEW 42 TARGET_SCHEMA MATERIALIZED VIEW 36
TARGET_SCHEMA PACKAGE 1 TARGET_SCHEMA PACKAGE 1
TARGET_SCHEMA PACKAGE BODY 1 TARGET_SCHEMA PACKAGE BODY 1
TARGET_SCHEMA PROCEDURE 11 TARGET_SCHEMA PROCEDURE 17
TARGET_SCHEMA SEQUENCE 581 TARGET_SCHEMA SEQUENCE 582
TARGET_SCHEMA TABLE 729 TARGET_SCHEMA TABLE 729
TARGET_SCHEMA TYPE 3 TARGET_SCHEMA TYPE 3
TARGET_SCHEMA VIEW 216 TARGET_SCHEMA VIEW 216

You can easily find the missing objects using the below command.

select object_name from dba_objects where owner like 'SOURCE_SCHEMA' 
minus
select object_name from dba_objects where owner like 'TARGET_SCHEMA' ;

If the target is present in another database using DBLINK.

select object_name from dba_objects where owner like 'SOURCE_SCHEMA' 
minus
select object_name from dba_objects@DBLINK where owner like 'TARGET_SCHEMA' ;

Thanks

 

Leave a Reply

Your email address will not be published. Required fields are marked *