Skip to Main Content
  • Questions
  • Oracle Edition Based Redefinition EBR Table Data

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Srinivasamurthy.

Asked: May 12, 2016 - 7:46 am UTC

Last updated: May 17, 2022 - 3:42 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi Tom,

I went through the articles & documentation, your article answers most of the issues

Asktom EBR Part 1 => http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o10asktom-172777.html
Asktom EBR Part 2 => http://www.oracle.com/technetwork/issue-archive/2010/10-mar/o20asktom-098897.html
Asktom EBR Part 3 => http://www.oracle.com/technetwork/issue-archive/2010/10-may/o30asktom-082672.html
and
Whitepaper on EBR
http://www.oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf

As per the documentation Table is an example of an noneditionable type.
https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS99923

The requirement is application upgrade also has some data changes in the application repository tables, its not structural table changes, its changes in the table data (both insert of new rows and updates/deletes of existing rows). How can this be restricted to new edition till the edition is actualized.

Is there a way or workaround, do we have any enhancement request or feature looking forward for making editionable table to solve this.

Regards,
SrinivasaMurthy

and Chris said...

EBR only supports the editioning of structural changes, not data. So you can't have different values in a table in different editions.

But you can simulate inserts and deletes using normal views. These are editionable. So you can have different where clauses in each edition. e.g.:

-- in edition 1
create or replace view v as
  select * from tab
  where  <rows to show in ed 1>

-- in edition 2
create or replace view v as
  select * from tab
  where  <rows to show in ed 2>


Then point your app at the view instead of the table. Of course, this means you'll never be able to see rows that don't match the where clause in each edition. So use with care. This is best suited to reference or read-only tables.

Another option is editioning views can point to different tables in different editions! So you can clone your table, doing the changes as part of this.

-- in edition 1
create or replace editioning view v as
  select * from old_tab

-- in edition 2
create or replace editioning view v as
  select * from _new_tab


You can then use cross-edition triggers to maintain the data as needed.

Again, use with care. This complicates your system. You need two copies of your table at the same time. The extra storage may rule this out for large tables. You'll also potentially break foreign keys that point to the table as you could have rows in the old but not the new and vice-versa.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

wow, thanks for good workarounds for edition based redefinition of table

Srinivasamurthy Venkataramaiah, May 12, 2016 - 12:37 pm UTC

Thanks Chris for the answer and nice workarounds.
Still it will not serve the application upgrade purpose, until we have editionable table.
The requirement is application upgrade also has some data changes in the application repository tables, its not structural table changes, but table data changes (both insert of new rows and updates/deletes of existing rows). How can this be restricted to new edition till the edition is actualized.

I liked the first approach, why don't we have an enhancement keep a additional column somewhere for keeping edition#, show only those rows in the edition1, and follow your first approach of creating views with where for old and for next new one put edition2 for edition#...so on.. :)

Wow as Tom mentioned, its a killer feature for application upgrade.

By the time i got your reply, I gave the below workaround/solution, let me wait for the customer feedback.
I m pasting here, if anyone has this kind of questions.

Some how we need to encapsulate the new data changes inside a new editionable procedure created only in child edition 2 and run that procedure after new edition actualized. This will eliminate people in base edition 1 to see and run it accidentally.
if the new data changes are huge, create a new table similar to old say T2_ and create editionable view T2 for this new table T2_ and then in child edition 2 a new editionable procedure to populate the old or T1 editionable view from T2 editionable view.
Once the data changes happens drop the T2, T2_ and the new editionable procedure to do data changes.

Chris Saxon
May 12, 2016 - 3:04 pm UTC

I'm not following your workaround with the two tables. How does that work exactly?

Thanks Chris, your idea is wonderful

Srinivasamurthy Venkataramaiah, May 12, 2016 - 12:56 pm UTC

I gave this based on your feedback

Add a column to existing table T1_ say ed_number and update the whole table with 1 and somehow make it to contain value 1 for all the user inserts (i.e., new rows) in edition1
create new normal view T1 with where condition to include only ed_number=1 (which is editionable ! ), then make application (like procedures/functions) to point to use this view

in the edition2 change the view to include only ed_number=2
do all your code changes, after actualize, update ed_number to 2 for all the rows, if this update is costly thing to do, then modify view again with where condition ed_number=1 or 2. ;)

Chris Saxon
May 12, 2016 - 3:05 pm UTC

Yes, you could add a column to the table to say which edition the data should be shown in.

But like I said, take care if going down this route. You can easily end up in a tangled mess if you're not careful...

clarification

Srinivasamurthy Venkataramaiah, May 12, 2016 - 3:23 pm UTC

1. some how we need to encapsulate the new data changes inside a new editionable procedure created only in child edition 2 and run that procedure after new edition actualized. This will eliminate people in base edition 1 to see and run it accidentally.

2. if the new data changes are huge, create a new empty table similar to old table T1_ say T2_ (create table T2_ select * from T1_ where 1=2) and create editionable view T2 for this new table T2_ and then in child edition2 a new editionable procedure to populate (with applicable business conditions if any) the old table T1_ or T1 editionable view from T2 editionable view.
Once the data changes happens drop the T2, T2_ and the new editionable procedure to do data changes.
Besically T2_ is a place holder table to hold application data changes, which is hidden from edition1 application users, they will have access to T1 view and not T2 view.


Chris Saxon
May 12, 2016 - 4:15 pm UTC

I don't understand. If the app doesn't have access to T2 in edition 2, how does it see the new data?

As soon as you update a table, the data is visible to *both* editions.

Assuming you have sessions using both editions while your upgrade is in progress, one will see the "wrong" data.

If you don't and there's just a cut-over where no one accesses the database, it seems you're over complicating to me. Just have a brief outage and do the update in this.

Or you could create T2 as a copy of T1 and update the data. Then in edition 2 point editioning view T1 at T2. Users in edition 1 will still see the data from T1.

This enables you to have different data in the "same table" across editions. Assuming your constraints allow this of course!

Virtual Private DB?

Stephen M, May 12, 2016 - 6:07 pm UTC

Could a Virtual Private Database help with data changes where each table has an additional column that represents the edition this row is effective for?

The added VPD predicate could then add to each statement something like "edition <= CURRENT_EDITION" where CURRENT_EDITION is the effective edition for that user?

Stephen
Chris Saxon
May 13, 2016 - 9:34 am UTC

If you add a column to say which edition the rows should be visible in, potentially that could work using the CURRENT_EDITION_NAME value from USERENV.

It's adding complexity to an already complex solution though. For me this is a step too far.

Thanks for valuable inputs. Online application upgrade using Edition-Based Redefinition EBR

Srinivasamurthy Venkataramaiah, May 13, 2016 - 10:21 am UTC

Thanks Chris & Stephen for valuable inputs.
Let us check, how customer uses mix of oracle technologies to accomplish their Online application upgrade or patching using Edition-Based Redefinition EBR, VPD, etc.

Ya, CURRENT_EDITION_NAME value from USERENV adds complexity, its upto customer. I also suggest to use their application version number or build number, because its basically their application repository data modifications

What was the solution?

Shaina, January 17, 2017 - 12:28 pm UTC

Hi,

I would like know that how was this problem resolved ultimately. I am facing the same issue using EBR, where I have some DMLs along with DDLs to upgrade the application version.

Thanks,
Shaina
Connor McDonald
January 18, 2017 - 12:32 am UTC

You'll need to hope that Srinivasamurthy shares his progress with us.

EBR : table with column datatype as a type object

Prafull Nandanwar, May 11, 2022 - 6:50 am UTC

Hi,

We are enabling EBR on 19C . Have encountered following case in our legacy application.

We have a table with column datatype as a type object.
After EBR is enabled, this table is marked as invalid.
This table is refered/used by large no. of functions and procedures. Hence, all these are referencing objects are now invalid.
We cannot change this implementation as this will impact critical business functionality.


Is there any workaround or handling that can be done to manage this case without invalidation of such tables table with column datatype as a type object??

Please suggest.


OBJECT_NAME : TABLE1
TYPE : TABLE
STATUS : INVALID
COLUMN_NAME : ADDITIONAL_DETAILS
DATA_TYPE : TYPE_ADDITIONAL_DETAILS




CREATE TYPE TYPE_ADDITIONAL_DETAILS
AS OBJECT
( id NUMBER(6)
, item_code VARCHAR2(50)
, item_name VARCHAR2(50)
, teritory_code VARCHAR2(50)
, tax_code VARCHAR2(50)
, tax_country_code VARCHAR2(50)
) ;
Chris Saxon
May 11, 2022 - 1:10 pm UTC

Make the object type NONEDITIONED:

CREATE NONEDITIONED TYPE TYPE_ADDITIONAL_DETAILS ...


Unfortunately you can't change (NON)EDITIONED once you've editioned enabled the user. You have to drop and re-create the type.

So you're in a bit of a mess sadly - you may have to restore from a backup to get this working again. I don't know of a better solution; speak to support to see if they can help you.

Reply - EBR : table with column datatype as a type object

A reader, May 17, 2022 - 5:34 am UTC

Thanks!!

Issue resolved

We followed below steps before enabling all the schemas as EBR.
a) Restored schema
b) Before enabling EBR,
- Dropped above tables (refering type as column)
- Dropped user defined types
- Re-created types as as NONEDITIONABLE
c) Enabled EBR

Now, table is nit showing as invalid.

Thanks,
Prafull



Chris Saxon
May 17, 2022 - 3:42 pm UTC

Glad you got it resolved

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library