DATAPUMP's SOURCE EDITION AND TARGET EDITION

                    ORACLE 11g DATAPUMP SOURCE EDITION AND TARGET EDITION 

Hi Guys,

First I would like to explain what do you mean by edition .  Simple definition is same object  name you can store multiple times. This will useful during upgradation.  For example, as per your not sure whether object is required extra structure or not, in this case create same object name with different edition. Once you finalised then you can drop the unused object name .

Editions are nonschema objects; as such, they do not have owners. Editions are created in a single namespace, and multiple editions can coexist in the database.

The database must have at least one edition. Every newly created or upgraded Oracle Database starts with one edition named ora$base.

Edition-based redefinition enables you to upgrade the database component of an application while it is in use, thereby minimizing or eliminating down time.

To upgrade an application while it is in use, you copy the database objects that comprise the application and redefine the copied objects in isolation. Your changes do not affect users of the application—they continue to run the unchanged application. When you are sure that your changes are correct, you make the upgraded application available to all users.

Using edition-based redefinition means using one or more of its component features. The features you use, and the down time, depend on these factors:

·         What kind of database objects you redefine

·         How available the database objects must be to users while you are redefining them

·         Whether you make the upgraded application available to some users while others continue to use the older version of the application

You always use the edition feature to copy the database objects and redefine the copied objects in isolation; that is why the procedure that this chapter describes for upgrading applications online is called edition-based redefinition.

If the object type of every object you will redefine is editionable, the edition is the only feature you use.

Table is not an editionable type. If you change the structure of one or more tables, you also use the editioning view feature.

If other users must be able to change data in the tables while you are changing their structure, you also use forward crossedition triggers. If the pre- and post-upgrade applications will be in ordinary use at the same time (hot rollover), you also use reverse crossedition triggers.  Crossedition triggers are not a permanent part of the application—you drop them when all users are using the post-upgrade application.

An editioned object is a schema object that has both an editionable type and an editions-enabled owner. (A schema object that has an editionable type but not an editions-enabled owner is potentially editioned.) An edition can have its own copy of an editioned object, in which case only the copy is visible to the edition.

A noneditioned object is a schema object that has a noneditionable type. An edition cannot have its own copy of a noneditioned object. A noneditioned object is identical in, and visible to, all editions.

An editioned object is uniquely identified by its OBJECT_NAME, OWNER, and EDITION_NAME. A noneditioned object is uniquely identified by its OBJECT_NAME and OWNER —its EDITION_NAME is NULL. You can display the OBJECT_NAME, OWNER, and EDITION_NAME of an object with the static data dictionary views  dba_objects.

The objects are limited to some set of objects which is not having the storage.

 Editionable and Noneditionable Schema Object Types

These schema objects types are editionable:

• SYNONYM
• VIEW
• All PL/SQL object types:
• FUNCTION
• LIBRARY
• PACKAGE and PACKAGE BODY
• PROCEDURE
• TRIGGER
• TYPE and TYPE BODY

All other schema object types are noneditionable. Table is an example of an noneditionable type.

A schema object of an editionable type is editioned if its owner is editions-enabled; otherwise, it is potentially editioned.

A schema object of a noneditionable type is always noneditioned, even if its owner is editions-enabled. A table is an example of an noneditioned object.

STEP1: Enabling Editions for a User

To enable editions for a user, use the ENABLE EDITIONS clause of either the  CREATE  USER  or  ALTER  USER statement. The EDITIONS_ENABLED column of the static data dictionary view  DBA_USERS  or USER_USERS shows which users have editions enabled.

[oracle@inas ~]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 18 09:44:30 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> GRANT CREATE ANY EDITION, DROP ANY EDITION to dpusr;

Grant succeeded.

SQL> alter user dpusr enable editions;

User altered.

SQL> grant create any view to dpusr;

SQL> select USERNAME,EDITIONS_ENABLED from dba_users where USERNAME='DPUSR';

USERNAME                       E

------------------------------ -

DPUSR                          Y

STEP2:  Check the current & new edition in DPUSR 

[oracle@inas ~]$ sqlplus dpusr/dpusr

SQL> create edition e2;     ---creating new edition

SQL> SELECT SYS_CONTEXT('userenv', 'current_edition_name') FROM DUAL;    -- check the current edition

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')

--------------------------------------------------------------------------------

ORA$BASE

SQL> create editioning view ed_emp_view_ORABASE as select  EMPNO,ENAME  from emp;

View created.

SQL> desc ed_emp_view_ORABASE

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

SQL> alter session set edition=e2;    ---moving to new edition

Session altered.

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

SYS_CONTEXT('USERENV','CURRENT_EDITION_NAME')

--------------------------------------------------------------------------------

E2

SQL> CREATE or replace EDITIONING VIEW ed_emp_view_ORABASE as select  EMPNO,ENAME,SAL,COMM from emp;           ----here create or replace command is required else it will show already objects exists.

View created.

SQL> desc TR

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 SAL                                                NUMBER(7,2)

  

STEP3: Take the export 

oracle@inas ~]$ expdp dumpfile=d4.dmp schemas=dpusr include=view directory=dps source_edition=e2 

Export: Release 11.2.0.3.0 - Production on Wed Sep 18 10:27:26 2013

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

Username: dpusr

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "DPUSR"."SYS_EXPORT_SCHEMA_05":  dpusr/******** dumpfile=d4.dmp schemas=dpusr include=view directory=dps source_edition=e2

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Master table "DPUSR"."SYS_EXPORT_SCHEMA_05" successfully loaded/unloaded

******************************************************************************

Dump file set for DPUSR.SYS_EXPORT_SCHEMA_05 is:

  /u01/apps/home/oracle/dp/d4.dmp

Job "DPUSR"."SYS_EXPORT_SCHEMA_05" successfully completed at 10:27:38

STEP4:  drop the view from any edition

[oracle@inas ~]$ sqlplus dpusr/dpusr

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 18 10:28:03 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set edition=ORA$BASE;

Session altered.

SQL> desc TR

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

SQL> drop view TR;

View dropped.

Now you lost object in ORA$BASE edition.   Now we have dumpfile backup but its in new edition.

Step5: IMPORT THE DUMPFILE

[oracle@inas ~]$ impdp dumpfile=d4.dmp directory=dps target_edition=ORA\$BASE

Import: Release 11.2.0.3.0 - Production on Wed Sep 18 10:29:55 2013

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

Username: dpusr

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "DPUSR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DPUSR"."SYS_IMPORT_FULL_01":  dpusr/******** dumpfile=d4.dmp directory=dps target_edition=ORA$BASE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Job "DPUSR"."SYS_IMPORT_FULL_01" successfully completed at 10:30:02

STEP 6: Verify it

[oracle@inas ~]$ sqlplus dpusr/dpusr

SQL*Plus: Release 11.2.0.3.0 Production on Wed Sep 18 10:30:13 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER SESSION SET EDITION =ORA$BASE;

Session altered.

SQL> desc TR

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                              NUMBER(4)

 ENAME                                              VARCHAR2(10)

 SAL                                                NUMBER(7,2)

Note:  Now you got the object from dumpfile which is target edition dumpfile.  Now source edition and target edition objects are same.