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.
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. ;)
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.
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
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
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)
) ;
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
May 17, 2022 - 3:42 pm UTC
Glad you got it resolved
In oracle ebr How can we alter table column size with out impating old editions
Venkat, March 15, 2024 - 11:29 am UTC
March 15, 2024 - 6:16 pm UTC
The basic process is:
- Add a new column to the table with the new size
- Create a new edition
- In this new edition, create an editioning view that selects the new column instead of the original
- Have cross edition triggers to write to old and new columns as appropriate
So if the column was INFO VARCHAR2(100), you add INFO_NEW VARCHAR2(4000). The editioning view in the new edition selects INFO_NEW instead of INFO:
create view ... (... info) as select ..., INFO_NEW from...
A reader, March 20, 2024 - 1:25 pm UTC
Thank you for your Responce. I feel this option is good when we have limited Rows in table. is tehre any other options if we want to do same kind of change on transaction tables which has milions of rows?.
March 25, 2024 - 4:01 pm UTC
Why do you think having millions of rows in the table makes this process unusable?