8 Replies Latest reply on Apr 28, 2014 10:13 PM by rp0428

    Redefining a table with a user-defined type using DBMS_REDEFINITION

    1157483

      The application which we developed has one schema, USER1, which has tables, types, packages etc.

      I have to move some of the user-defined types to a different schema, USER2. USER1 has few tables which have a dependency on these user-defined types. We recreated the types in USER2, but before I drop the types in USER1, we have to remove the table's dependency on them. To do this, I am thinking of using DBMS_REDEFINITION by creating an intermediate table using the user defined type in USER2 (instead of USER1)

      I'm using the following to cast the user defined type in USER1 and to the new one created in USER2, where EMP_LIST is an array of EMP_TYPE.

      BEGIN DBMS_REDEFINITION.START_REDEF_TABLE( 'user1', 'department', 'department_in' , 'dept_id dept_id, dept_name dept_name , cast(emp_list as user2.emp_type) emp_list' , dbms_redefinition.cons_use_rowid); END;

      But, I'm getting the following error:

      Error : ORA-22907: invalid CAST to a type that is not a nested table or VARRAY

      I tried using the MULTISET operator but it looks as though it's not allowed. How could we achieve this?

        • 1. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION

          The application which we developed has one schema, USER1, which has tables, types, packages etc.

          I have to move some of the user-defined types to a different schema, USER2. USER1 has few tables which have a dependency on these user-defined types. We recreated the types in USER2, but before I drop the types in USER1, we have to remove the table's dependency on them.

          No - you don't 'have to move' them - perhaps you meant to say you 'want to' move them?

          Why do you want to move them? They are working just fine where they are and are in the SAME schema as the table that uses them. If you move them you will now have a dependency between objects in two different schemas and may end up needing additional grants and public synonyms to avoid having to specify the schema prefix when you reference them.

           

          To do this, I am thinking of using DBMS_REDEFINITION by creating an intermediate table using the user defined type in USER2 (instead of USER1)

          I don't know if you can use that package to do that. You may need to create a new column for the new object and just move the data. It also depends on the type of object and whether it is a collection.

          Or create a new table that uses the new TYPE and copy the data from the old table. For the new type column just provide the appropriate constructor  using the attributes from the old type column.

           

          I'm using the following to cast the user defined type in USER1 and to the new one created in USER2, where EMP_LIST is an array of EMP_TYPE.

          You can't CAST a type to a different type even if the structure is the same.

          1 person found this helpful
          • 2. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION
            1157483

            Why do you want to move them? They are working just fine where they are and are in the SAME schema as the table that uses them. If you move them you will now have a dependency between objects in two different schemas and may end up needing additional grants and public synonyms to avoid having to specify the schema prefix when you reference them.


            Thanks for the response, The reason for doing this is, to enable editions for user1, as table has dependency on a user defined type, uanble to enable the editions for the user1. Plan is to move the user defined type to another schema user2 (which will not be edition enabled). By enabling the editions for user1, we will be able to support online patching of the application (ex: creating the package body etc).


            Please let me know if there is any way to achieve this.


            I think, if there is no other option, we will have to remove the usage of user defined type in the table and create it as child table.

            • 3. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION
              The reason for doing this is, to enable editions for user1, as table has dependency on a user defined type, uanble to enable the editions for the user1. Plan is to move the user defined type to another schema user2 (which will not be edition enabled).

               

              Huh? Moving the type location doesn't remove the dependency. So how does that solve your problem?

               

              If your problem has to do with enabling editions then you need to post the actual exception you are getting and provide info about HOW and WHY you are enabling editions.

               

              If your problem is because you have an EVOLVED type then you may just need to reset it to version 1 using the ALTER TYPE RESET statement so that it is no longer evolved.

               

              It's hard to help with your problem when you won't tell us:

              1. EXACTLY what your problem is

              2. EXACTLY what exception you

              3. EXACTLY what 4 digit Oracle version you are using

               

              Post the DDL for the table and type so someone can at least try to reproduce the problem.

              • 4. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION
                1157483

                Below are the details:

                 

                User1 has the following objects:

                 

                CREATE TYPE employee_t AS OBJECT

                (employee_id NUMBER

                ,employee_name VARCHAR2(30)

                ,salary NUMBER

                ,dept_id NUMBER);

                 

                CREATE TABLE department

                (emp employee_t

                ,mgr varchar2(40)

                ,dept_id number

                ,dept_name varchar2(30));

                 

                When we try to enable editions for User1, we get, we use the force option table will be come invalid.

                ERROR at line 1:

                ORA-38819: user USER1 owns one or more objects whose type is editionable and

                that have noneditioned dependent objects


                ( ALTER TYPE RESET for the type gives following error, when there are dependent tables.

                ERROR at line 1:

                ORA-22374: cannot reset the version of a type with table dependents)

                 

                I tried the following,

                a. Recreated the type in User2;

                CREATE TYPE employee_t AS OBJECT

                (employee_id NUMBER

                ,employee_name VARCHAR2(30)

                ,salary NUMBER

                ,dept_id NUMBER);

                 

                b. Created a temp table in user1 using the type in user2

                CREATE TABLE department_tmp

                (emp user2.employee_t

                ,mgr varchar2(40)

                ,dept_id number

                ,dept_name varchar2(30));

                 

                c. Migrated the data using the script:

                begin

                for cr in (select * from department)

                loop

                dbms_output.put_line(cr.emp.employee_name);

                insert into department_tmp values (user2.employee_t(cr.emp.employee_id,cr.emp.employee_name,cr.emp.salary,cr.emp.dept_id),cr.mgr,cr.dept_id,cr.dept_name);

                commit;

                end loop;

                end;

                /

                 

                d. dropped the type employee_t and table department in user1

                e.renamed the table departmemt_tmp to department

                f. enabled editions user1, edition was enabled successfully.

                 

                Here I have taken a simple case, there are many such tables in our application(some with nest tables also), the above approach will not be easy. Hence I was exploring the possibility of using DBMS_REDEFINITION.

                 

                Please let me know if there is any way to achieve this.

                • 5. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION
                  Here I have taken a simple case, there are many such tables in our application(some with nest tables also), the above approach will not be easy.

                  Well - as I first said I don't know that you can use the package to do that.

                   

                  There will still be an architectural 'glitch' if you use the process you described. You will now have an editioned user that has a dependency of a non-editioned object in another schema.

                   

                  On the face of it that doesn't sound like a good idea - but that is for you to determine.

                   

                  My main concern is that you may have already made the decision to use EDITIONING but haven't really tested it properly yet.

                  By enabling the editions for user1, we will be able to support online patching of the application (ex: creating the package body etc).

                  That statement is curious because you said package 'body' when it is changes to a package spec that cause the most issues since that invalidates a lot of things.

                   

                  Do you really have a proven use case for using editioning? Or are you just exploring it as a possibility?

                  • 6. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION
                    1157483

                    Do you really have a proven use case for using editioning? Or are you just exploring it as a possibility?


                    I'm currently working on proof of concept, Based on the result of this exercise, decision will be made.

                    • 7. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION
                      Sven W.

                      rp0428 wrote:

                       

                      By enabling the editions for user1, we will be able to support online patching of the application (ex: creating the package body etc).

                      That statement is curious because you said package 'body' when it is changes to a package spec that cause the most issues since that invalidates a lot of things.

                       

                      Do you really have a proven use case for using editioning? Or are you just exploring it as a possibility?

                      But he is right. Editioning allows changes on packages even while the package is in use. So you can implement a change during some large batch run. And you can also avoid the issue of "session state of package has been changed".

                       

                      The change is only used once you make the new edition your default edition and when a new session is created, Older sessions are not influenced.

                       

                      Editions are cool!

                      • 8. Re: Redefining a table with a user-defined type using DBMS_REDEFINITION

                        Sven W. wrote:

                         

                        rp0428 wrote:

                         

                        By enabling the editions for user1, we will be able to support online patching of the application (ex: creating the package body etc).

                        That statement is curious because you said package 'body' when it is changes to a package spec that cause the most issues since that invalidates a lot of things.

                         

                        Do you really have a proven use case for using editioning? Or are you just exploring it as a possibility?

                        But he is right. Editioning allows changes on packages even while the package is in use. So you can implement a change during some large batch run. And you can also avoid the issue of "session state of package has been changed".

                         

                        The change is only used once you make the new edition your default edition and when a new session is created, Older sessions are not influenced.

                         

                        Editions are cool!

                        They are about as 'cool' as triggers. And there is a pretty good consensus about how much trouble triggers can cause. The only reason, IMHO, that there haven't been more horror stories about editioning is because it has, thankfully, such limited use to date.

                         

                        Editions are a disaster waiting to happen if not designed and implemented properly.

                         

                        The bad news is that it is too easy to design and implement them improperly. Then you can have a nightmare trying to determine what the problem is and fixing it..

                         

                        That is my main concern based on what OP posted. It isn't clear that they have an appropriate use case for editioning. Two more red flags for me are 1) the use of object type column in a relational table and 2) the willingness to move that non-editioned object type to another schema just to be able to create an editioned user.

                         

                        That introduces a dependency between two schemas where none existed before and requires new grants and possibly a new synonym. Assuming that I wanted to edition that user the first thing I would have looked at was eliminating the use of the object type altogether in favor of a 'child' table to hold the data,

                         

                        The ability to run simultaneous multiple editions of code may seem attractive but there can be many side effects that aren't at all obvious to users new to editioning. This paragraph from the Advanced Appication Developer Guide illustrates just how confusing, and potentially disastrous, editioning can become:

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

                        Crossedition Trigger Execution

                        A crossedition trigger runs using the edition in which it was created. Any code that the crossedition trigger calls (including package references, PL/SQL subprogram calls, and SQL statements) also runs in the edition in which the crossedition trigger was created.

                        If a PL/SQL package is actual in multiple editions, then the package variables and other state are private in each edition, even within a single session. Because each crossedition trigger and the code that it calls run using the edition in which the crossedition trigger was created, the same session can instantiate two or more versions of the package, with the same name.

                        Good luck trying to train new developers how to work with that type of code!

                         

                        I have yet to see a use case that actually requires editioning. I suppose a true 24x7 application might be one but the many 24x7 apps I have seen have never been in operation 24x7 without a maintenance window of some sort.

                         

                        I'll stick to the 'tried and true' methods for now, thank you!