This discussion is archived
12 Replies Latest reply: Mar 31, 2013 12:16 PM by 632125 RSS

Edition-Based Redefinition

Rajat Newbie
Currently Being Moderated
Dear Experts,

I am working in a system which has one main schema called "Master" schema. When we create table and Complile procedures we do it in that schema.
There are several hundread users to the application who logged on to there own user name/ Own schema and access the table of Master schema. Public sysnonim of the tables are there for that.
They also executes procedures/Packages of Master Schema by public synonim.


But some times if any developer make any mistake in coding then we have to stop our application to compile the correct package.
Now if i want to use the feature of Edition-Based Redefinition to save the down time of the application , Could you please suggest me some way.

I tried to use the amazing feature in the development environment of Master schema.

Step 1:
create edition version2 as child of ora$base;
edition VERSION2 created.

Step 2:

SQL> ALTER USER master ENABLE EDITIONS FORCE;
ALTER USER MASTER ENABLE EDITIONS FORCE
*
ERROR at line 1:
ORA-38820: user has evolved object type


SQL> ALTER USER master ENABLE EDITIONS;
ALTER USER MASTER ENABLE EDITIONS
*
ERROR at line 1:
ORA-38819: user MASTER owns one or more objects whose type is editionable and
that have noneditioned dependent objects


In the second step i am getting the error.


My idea is i shall create a new version in master schema and apply the correct package in the corresponding version and our users are going to use the latest version without logging off from the application.

Could you suggest me some idea how can i implement it.

Regards
Rajat
  • 1. Re: Edition-Based Redefinition
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    See if this can help - http://longliveoracle.wordpress.com/tag/tom-kyte/

    Read the three links to Tom Kyte's articles first

    HTH
    Srini
  • 2. Re: Edition-Based Redefinition
    Rajat Newbie
    Currently Being Moderated
    Dear Srini,

    Thanks for the article you have suggested.

    But when i am performing the following steps i am getting such error.

    SQL> ALTER USER master ENABLE EDITIONS FORCE;
    ALTER USER MASTER ENABLE EDITIONS FORCE
    *
    ERROR at line 1:
    ORA-38820: user has evolved object type

    SQL> ALTER USER master ENABLE EDITIONS;
    ALTER USER MASTER ENABLE EDITIONS
    *
    ERROR at line 1:
    ORA-38819: user MASTER owns one or more objects whose type is editionable and
    that have noneditioned dependent objects

    Is that mean i am unable to get the benefit of the amazing feature.

    Regards
    Rajat
  • 3. Re: Edition-Based Redefinition
    Rajat Newbie
    Currently Being Moderated
    Any body have any idea. How to get it done.

    Regards
    Rajat
  • 4. Re: Edition-Based Redefinition
    Jos van den Oord Newbie
    Currently Being Moderated
    Hi,

    In the documentation you find the sollution:
    {You cannot enable editions for a user who owns one or more evolved ADTs. Trying to do so causes error ORA-38820. If an ADT has no table dependents, you can use the ALTER TYPE RESET statement to reset its version to 1, so that it is no longer considered to be evolved. (Resetting the version of an ADT to 1 invalidates its dependents.)}
    {A table cannot have a column of a user-defined data type (collection or ADT) whose owner is editions-enabled.}

    so use " ALTER TYPE RESET" statement to reset its version to 1

    Cheers,

    Jos van den Oord
    Blog : http://joordsblog.vandenoord.eu/
    Company : http://www.transfer-solutions.com/

    "Don't believe it, test it!"
  • 5. Re: Edition-Based Redefinition
    Rajat Newbie
    Currently Being Moderated
    dear jos van den oord

    Thanks you very much for your suggestion. Let me try it and get back to you as soon as posible.

    Regards
    Rajat
  • 6. Re: Edition-Based Redefinition
    Rajat Newbie
    Currently Being Moderated
    Dear experts,

    In the oracle doc it is written that

    "When a user is editions-enabled, every editionable-type object that the user has owned or will own is an editioned object."

    Is that mean if my master schema owns few table then the schema will never going to be edition enabled.

    Could you please suggest in this scenario what should be done by a dba to make it edition enabled.

    Regards
    Rajat
  • 7. Re: Edition-Based Redefinition
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Tables cannot be editioned - so if a schema contains only tables, what is the point of enabling editioning on that schema ?

    HTH
    Srini
  • 8. Re: Edition-Based Redefinition
    Rajat Newbie
    Currently Being Moderated
    Actually it not only owns some table it also owns some packages.
    I want to change the packages only without the downtime.

    Now the packages have public synonym. The others schema ( Basically users) access the packages of master schema with that public synonym.

    In my idea is

    1> I shall make the master schema edition enabled.
    2> After the production release if user report any issue i shall make a new edition of master schema.
    3> In that edition i shall compile the correct package.
    4> After that i shall allow user to access the new package of new edition of master schema.

    Could you please suggest whether i am in a right path or not.

    Regards
    Rajat

    Edited by: Rajat on Mar 30, 2013 7:09 PM
  • 9. Re: Edition-Based Redefinition
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Your process looks correct - have you reviewed the example link I posted above ? What have you tried so far ?

    HTH
    Srini
  • 10. Re: Edition-Based Redefinition
    damorgan Oracle ACE Director
    Currently Being Moderated
    The issue is that you have objects that are not editionable utilizing editionable objects. For example an object-table or a function based index using a user defined PL/SQL function. Look for non-editionable objects with dependencies on editionable objects.

    I have many pages of demos on my website:
    http://www.morganslibrary.org/reference/ebr.html

    and highly recommend you read Bryn Llewellyn's editioning white paper you will find a link on my home page:
    http://www.morganslibrary.org/
  • 11. Re: Edition-Based Redefinition
    Rajat Newbie
    Currently Being Moderated
    Dear experts,

    Thanks all for the help . Currently i am able to make the MASTER schema edition enabled.


    But i have also observed that when i am trying to execute any procedure in another session
    it is showing an error. The package is own by the master schema and other schemas access it by public synonym.
    after master schema is edition enabled system is showing the error.

    identifier 'SC_TEST' must be declared

    as per oracle doc
    " o     A public synonym cannot refer to an editioned object.".

    there are hundreads of packages in the Master schema and i am asking is there exists any way which will take the Master schema to it's previous state i.e before edition enabled.

    Regards
    Rajat
  • 12. Re: Edition-Based Redefinition
    632125 Newbie
    Currently Being Moderated
    Any body has any idea what could be my next move.

    Regards
    Rajat

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points