12 Replies Latest reply: Mar 31, 2013 2:16 PM by user629122 RSS

    Edition-Based Redefinition

    Rajat
      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
          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
            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
              Any body have any idea. How to get it done.

              Regards
              Rajat
              • 4. Re: Edition-Based Redefinition
                Jos van den Oord
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                user629122
                                Any body has any idea what could be my next move.

                                Regards
                                Rajat