This is part 8 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

I concluded the previous post by stating that the application code should never reference tables directly; instead, every table should be covered by an editioning view and the application code should reference the views.

Starting from Scratch?

If you start developing a new system from scratch, I strongly recommend to follow this practice, even if initially there is no requirement for online upgrades. That way, you will be ready for online upgrades from day one, so should such a requirement arise at a later stage you will not need to change anything – neither your data structures nor your code. The only overhead is maintaining an editioning view for every table, and I’m sure you’ll realize very quickly that this overhead is negligible. As long as downtime during your upgrades is acceptable, you only need to enable your schema owner for editions (a one-time operation, as shown in part 3), and (by default) you will simply use a single edition – the default one.

Existing Systems

If you have an existing system, and you want to enjoy the benefits of online upgrades and zero downtime, you need to do a one-time conversion of your schema – to cover all the tables by editioning views. Actually, even if you have an existing system and you do not want to enjoy these benefits, I would still recommend getting ready for online upgrades now. The rationale is the same as in the previous section (“Starting from Scratch?”).

Converting the Demo Schema

Let’s handle first the demo schema I’ve been using in this post series. We currently have the following objects:

V2>  select o.object_type,
  2          o.object_name,
  3          o.status,
  4          o.edition_name,
  5          nullif(
  6            d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7            ' []'
  8          ) depends_on
  9   from   user_objects      o,
 10          user_dependencies d
 11   where  d.name(+) = o.object_name
 12   and    d.type(+) = o.object_type
 13   and    d.referenced_owner(+) = user
 14   order by nvl2(o.edition_name, 2, 1),
 15            o.object_type,
 16            o.object_name,
 17            d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE       VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   V2           PEOPLE [table]

PACKAGE BODY APP_MGR      VALID   V2           APP_MGR [package]
                                               PEOPLE_DL [package]

             PEOPLE_DL    VALID   V2           PEOPLE [table]
                                               PEOPLE_DL [package]


8 rows selected.

I will change now the name of the table from PEOPLE to some other name – PEOPLE$0 in this example, and then I’ll create an editioning view named PEOPLE, that projects all the columns from PEOPLE$0:

V2> rename people to people$0;

Table renamed.

V2> create editioning view people as
  2    select * from people$0;

View created.

This method – giving the editioning view the previous name of the table – allows us to make no changes in the packages. The only thing I’ll need to do is recompiling the packages, as they became broken as soon as the table was renamed. I’ll use the revalidate.sql script that I showed in part 5.

V2> @revalidate.sql

PL/SQL procedure successfully completed.

V2>  select o.object_type,
  2          o.object_name,
  3          o.status,
  4          o.edition_name,
  5          nullif(
  6            d.referenced_name || ' [' || lower(d.referenced_type) || ']',
  7            ' []'
  8          ) depends_on
  9   from   user_objects      o,
 10          user_dependencies d
 11   where  d.name(+) = o.object_name
 12   and    d.type(+) = o.object_type
 13   and    d.referenced_owner(+) = user
 14   order by nvl2(o.edition_name, 2, 1),
 15            o.object_type,
 16            o.object_name,
 17            d.referenced_name;

OBJECT_TYPE  OBJECT_NAME  STATUS  EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX        PEOPLE_PK    VALID

TABLE        PEOPLE$0     VALID

PACKAGE      APP_MGR      VALID   ORA$BASE

             PEOPLE_DL    VALID   V2           PEOPLE [view] 

PACKAGE BODY APP_MGR      VALID   V2           APP_MGR [package]
                                               PEOPLE_DL [package]

             PEOPLE_DL    VALID   V2           PEOPLE [view] 
                                               PEOPLE_DL [package]

VIEW         PEOPLE       VALID   V2           PEOPLE$0 [table] 


9 rows selected.

Note that now the PEOPLE_DL package spec and package body depend on the PEOPLE editioning view and not on the table. The only object that depends on the table is the editioning view itself.

The Conversion Process in General

The conversion process has three major steps:

1. Covering each table by an editioning view
2. Replacing all the references to tables in the source code (procedures, functions, packages, types, trigger bodies, regular views, synonyms) with references to editioning views
3. Moving relevant entities that are “owned” by tables to be owned by editioning views (namely: triggers, privileges and VPD policies)

Note that this process (at least the straightforward way to implement it) is not an online upgrade and does require downtime. However, once done, that was your last (planned) downtime.

Step 1: covering each table by an editioning view

The example above is obviously very simple. In a real system we naturally have more than one table, and potentially many objects of various types.
So it makes sense to write a script that iterates over all the tables and handles them one by one. Here is one suggestion for such a script:

declare
    g_too_long exception;
    pragma exception_init(g_too_long, -972);
    l_not_handled varchar2(1000);
begin
    for l_tab in (
      select table_name from user_tables
      minus
      select table_name from user_editioning_views)
    loop
        begin
            execute immediate 'rename ' || l_tab.table_name || ' to '
                              || l_tab.table_name || '$0';
            execute immediate 'create editioning view ' || l_tab.table_name ||
                              ' as select * from ' || l_tab.table_name || '$0';
        exception
            when g_too_long then
                l_not_handled := l_not_handled || ',' || l_tab.table_name;
        end;
    end loop;
    if l_not_handled is not null then
        raise_application_error(-20000,
                                'please handle the following tables manually: ' ||
                                substr(l_not_handled, 2));
    end if;
end;
/

Step 2: replacing all the references to tables in the source code with references to editioning views

If in step 1 we rename the tables and use their original names for the editioning views (as done in the examples above), then step 2 becomes as easy as recompiling all the broken objects. This will fix all the references automatically.

Step 3: moving relevant entities that are “owned” by tables to be owned by editioning views

If triggers are defined on the tables, they should be dropped and recreated on the corresponding editioning views instead.
If VPD policies are defined on the tables, they should be dropped and recreated on the corresponding editioning views instead.
If any privileges are granted on the tables, they should be revoked and re-granted on the corresponding editioning views instead.

Note that other entities, such as indexes and constraints, remain belonging to the tables.

For other parts of the EBR series, please visit the index page.