Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, victor.

Asked: March 28, 2014 - 2:22 pm UTC

Last updated: April 16, 2014 - 4:15 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Tom,

When we create the very first edition as a child of ORA$BASE, and grant some package to it or re-compile some package, this package gets assigned to our new edition as expected:

create edition v36 as child of ora$base;
alter session set edition=v36;
grant execute on P_CONF_MERCH_ETL to IHUB_ETL;

Now, what is the pro and con of running a LOGON trigger to enable v36 for all new logon to Oracle, vs. doing ALTER SYSTEM SET DEFAULT EDITION = v36 ?

and how can we 'purge' older editions, if every new edition we create always 'is a child of ' the older edition? Oracle will not let us drop those older editions, and is this a problem at all, if say we create 20 new editions per year, for 5 years?

How do we manage completely non-editionable objects such as TYPES and AQ queues, and sequences? anything special to consider as we implement EBR for all of our OWNER schema for these non-editionable types? I know function based index will raise an error because INDEX is non-editionable, and function is, so if you can re-write the function as a inline CREATE INDEX expression, it will solve the issue. but what if you cannot?

sorry, that is more than 1 question.. Purging EBR is the main question.
thanks in advance!




and Tom said...

The pro's and con's of using a logon trigger versus updating a service attribute at the TNS level versus setting the default edition are very application specific. What are the needs of your application?

If you truly want all new connections to use the new version, setting the default edition would make sense.

If you want new application connections under some certain circumstances to use the new edition, using a logon trigger where you can put some logic like "if the user is X or the ip_address is Y or ..." for testing purposes - would make sense.

If you want a specific application to use the new edition, but are not ready for all applications to use it, using a service attribute in the TNS connection for that application would make sense.

and so on - the pro's and con's (as is true for most everything pro/con wise) would depend on your needs.


In the general case - I would say that changing the default database edition is the typical course of action, coupled with a bounce of the application servers to re-establish the connections, using the new edition.


You can certainly drop old editions - as soon as v$session shows that the old edition is no longer in use by any session (and a few other rules).

http://docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_editions.htm#ADFNS99919

The old edition will be dropped. Any object specific to that edition (not over-ridden in the next edition, the first child of it) will likewise be dropped using the cascade option.

If you do not drop the old editions, those objects will stick around. the downside to that is your data dictionary might be larger than it would otherwise be, but performance wise, it is not really going to impact you.


As far as "how to handle non-editionable objects" - it would again require a specific case. For tables, it would rely on editioning views and perhaps cross edition triggers and definitely some heavy thinking/design. Not sure what needs of a sequence change would be. For the function based index - you would create a NEW (probably invisible) index using the new logic and as part of the upgrade - you'd be dropping the old one (we have to rebuild that index if the underlying logic has changed). And so on - we'd need a use case (set of circumstances) to describe what might the approach to take for each...

Rating

  (2 ratings)

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

Comments

EBR

victor feinstein, March 31, 2014 - 1:04 pm UTC

very helpful

EBR purging follow up

victor feinstein, April 13, 2014 - 4:01 pm UTC

Hi Tom, very helpful comments regarding setting edition as default, but i still lack clarity how i can drop prior edition that i created as a child of ORA$BASE, and now I also have a newer edition which is a child of the one i need to purge.

in effect, i have ORA$BASE --> V37 --> V38, and I actualized all the objects into v37 to make sure it does not 'inherit' any from ORA$BASE, and v38 is fully actualized as well.

It still does not let me drop v37..
drop edition v37 cascade;
SQL Error: ORA-38810: Implementation restriction: cannot drop edition that has a parent and a child

Now, I am concerned with leaving all these prior editions around without abilities to purge them, as they will seriously clutter up my system in the course of several years, we typically do a release every 2 weeks..

help!

Tom Kyte
April 16, 2014 - 4:15 pm UTC

I don't see an issue with "clutter", the data dictionary is built as an OLTP like application - it scales over time (all indexed access, few rows returned). It might affect a tool that ad-hocs the dictionary over time and does queries that return an ever growing set of data (our dictionary queries won't really return an every growing set of data).

you'd have to go back and drop the root (please make sure you are patched up to 11.2.0.3+, or reference Bug 10329257 before dropping ora$base) to drop the chain of editions you no longer wish to have



more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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