Oracle Applications - How to get the value of zd_edition_name

In Oracle Applications 12c release 1 there is a new column that forms part of many primary keys called zd_edition_name. This relates to editions that you can have for keeping the database up during changes. So you would have two editions, you can make changes to the non-live on and then just live swap over when you are done (my limited understanding - I am not a dba).

My questions is how can I get the value of zd_edition_name, since this is now part of the primary key and also because tables like fnd_descr_flex_col_usage_tl would bring back two rows instead of one if you don't pass the value of zd_edition_name.

Also what does the zd stand for?

Solution

EBS and Edition Base Redefinition and Online Patching

The column, zd_edition_name, is just a component of the edition based redefinition feature of an Oracle 11G 2 (or greater) database as you have indicated.

Oracle Applications does not leverage this edition based redefinition database feature until 12.2 EBS.

The apps owned synonym will display the run time value, SET1 or SET2. It will be one value. For EBS 12.1, I would expect the run time value to be SET1.

APPS@db>    select
  2      zd_edition_name
  3      from
  4      fnd_descr_flex_col_usage_tl
  5      group by   zd_edition_name;
ZD_EDITION_NAME
SET1

With the editionable view and the table, we do not have that restriction:

APPS@db>SELECT
  2      zd_edition_name
  3  FROM
  4      applsys.fnd_descr_flex_col_usage_tl
  5  GROUP BY
  6      zd_edition_name;
ZD_EDITION_NAME
SET2
SET1

In EBS 12.2, one could identify the active file system which should have a correspondence with SET1/SET2 through logging in to the Oracle Apps server(s) and echoing the environment variables:

$FILE_EDITION = patch

$RUN_BASE = /u01/R122_EBS/fs1

$PATCH_BASE = /u01/R122_EBS/fs2

By querying the apps owned synonym, this is unnecessary to know the value of ZD_EDITION_NAME (it is a value associated with the run edition which will be the value).

You can view the editionable objects associated with table with a query like this:

APPS@db>VAR b_object_name varchar2(30);
APPS@db>EXEC :b_object_name:= 'FND_DESCR_FLEX_COL_USAGE_TL';

PL/SQL procedure successfully completed.

APPS@db>SELECT
  2      ao.owner,
  3      ao.object_name,
  4      ao.object_type
  5  FROM
  6      all_objects ao
  7  WHERE
  8      1 = 1
  9      AND   owner IN (
 10          'APPS',
 11          'APPLSYS'
 12      )
 13      AND   ao.object_name IN (
 14          :b_object_name,
 15          substr(:b_object_name,1,29)
 16          || '#'
 17      );
OWNER     OBJECT_NAME                    OBJECT_TYPE   
APPLSYS   FND_DESCR_FLEX_COL_USAGE_TL    TABLE         
APPLSYS   FND_DESCR_FLEX_COL_USAGE_TL#   VIEW          
APPS      FND_DESCR_FLEX_COL_USAGE_TL    SYNONYM

Here are list of versions existing in an EBS instance:

APPS@db>SELECT
  2      level,
  3      de.edition_name,
  4      de.parent_edition_name
  5  FROM
  6      dba_editions de
  7  START WITH
  8      de.edition_name = 'ORA$BASE'
  9  CONNECT BY
 10      PRIOR de.edition_name = de.parent_edition_name
 11  ORDER BY
 12      de.edition_name;
  LEVEL EDITION_NAME      PARENT_EDITION_NAME
      1 ORA$BASE
      2 V_20160703_2120   ORA$BASE
      3 V_20160708_1723   V_20160703_2120
    ...
     29 V_20180117_1118   V_20171206_1115
     30 V_20180130_0107   V_20180117_1118

For an 12.1 EBS environment, I would expect the starting edition, ORA$BASE, to be the only edition.