What is Data Pump Expdp SOURCE_EDITION and Impdp TARGET_EDITION Options? Examples


In Oracle 11gR2 you can create and keep different versions or editions of objects. The objects are limited to some set of objects which is not having the storage. You can see more details here with examples.Oracle Object Editions

Now let’s discuss about the data pump parameters expdp source_edition and impdp target_edition. Oracle provides the provision for different versions of same object under different editions. Suppose if you wanted to backup and restore these versions into across different editions using these options.

During the application development you can create multiple versions of objects for different releases. So in case if you wanted to migrate code from one version to different version within database or across database you can use this option.

See some examples.
I have 2 editions of one view called ED_EMP_VIEW_ORABASE in my schema. One is with default edition called ORA$BASE and another in NEW_EDITION edition with different columns from emp table. In below example I am trying to export views from scott schema from NEW_EDITION to ORA$BASE edition.

SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.

SQL> desc ed_emp_view_ORABASE
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)

SQL> ALTER SESSION SET EDITION =NEW_EDITION;

Session altered.

SQL> desc ED_EMP_VIEW_ORABASE
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)

Exporting view
expdp job_name=exp1 schemas=scott dumpfile=exp_edition.dmp logfile=exp_edition.log include=view directory=EXP_DIR compression=all source_edition=NEW_EDITION

Export: Release 11.2.0.2.0 - Production on Mon Nov 19 04:54:34 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."EXP1":  /******** AS SYSDBA job_name=exp1 schemas=scott dumpfile=exp_edition.dmp logfile=exp_edition.log include=view directory=EXP_DIR compression=all source_edition=NEW_EDITION
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Master table "SYS"."EXP1" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.EXP1 is:
  /u01/zfssa/dev/backup1/export/exp_edition.dmp
Job "SYS"."EXP1" successfully completed at 04:54:41


Importing the dumpfile / views to ORA$BASE edition. You should remove the objects from the target edition before importing the dump. Otherwise, if the object present in the target edition you will get “ORA-31684: Object type VIEW:"SCOTT"."ED_EMP_VIEW_ORABASE" already exists” error.

SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.

SQL> drop view ed_emp_view_ORABASE;

View dropped.

impdp job_name=schemaexp full=y dumpfile=exp_edition.dmp logfile=impexp_edition.log directory=exp_dir TARGET_EDITION=ORA\$BASE

Import: Release 11.2.0.2.0 - Production on Mon Nov 19 05:04:26 2012

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

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SCHEMAEXP" successfully loaded/unloaded
Starting "SYS"."SCHEMAEXP":  /******** AS SYSDBA job_name=schemaexp full=y dumpfile=exp_edition.dmp logfile=impexp_edition.log directory=exp_dir TARGET_EDITION=ORA$BASE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Job "SYS"."SCHEMAEXP" successfully completed at 05:04:31

After the import you can verify the import after setting corresponding edition.

SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.
SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')
--------------------------------------------------------------------------------
ORA$BASE

SQL> desc ed_emp_view_ORABASE
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google