8 Replies Latest reply: Mar 11, 2013 8:15 AM by John Stegeman RSS

    Dropping index invalidates packages

    hurtn
      We have a custom ETL solution written in PLSQL. We were running fine on 9204 and have now upgraded our test warehouse DB to 10204. To avoid overhead we drop indexes, do the load, and then recreate them after. The problem we are facing is that after moving to 10g, everytime we drop an index it invalidates a package that might use the associated table resulting in ORA-06508: PL/SQL: could not find program unit being called as soon as the invalid package is then invoked. To me this seems very strange as according to the docs this should only happen if we have a view on the table, see http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10743/depend.htm#sthref1112 which we definitely do not have.
      As a work-around we are marking the index as unsuable before the load and then rebuilding after, but then of course one has to make all indexes non-unique in order for the skip_unused_indexes paramater to work... phew! Seems like a lot of effort for such a simple, common problem. Have we missed something? Any light on the matter would be appreciated.
        • 1. Re: Dropping index invalidates packages
          odie_63
          Hi,

          This is expected behavior, as per the documentation :
          When you drop an index, Oracle Database invalidates all objects that depend on the underlying table, including views, packages, package bodies, functions, and procedures.
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8015.htm#i2066885
          • 2. Re: Dropping index invalidates packages
            hurtn
            Thanks for your response but if you could elaborate further on the following:
            This suddenly changed in 10g, is there no work-around besides the option below?
            In a warehouse environment, Is the recommended approach to disable and rebuild indexes then, instead of dropping, and then also to use non-unique indexes with unique constrainsts?
            • 3. Re: Dropping index invalidates packages
              Ghulam Mustafa Butt
              If you just want to reset the index you can write a procedure as follows: This procedure will reset the index given in the input parameter...

              Hope this helps:

              Thanks
              Ghulam
                 PROCEDURE reset_seq
                         ( p_seq_name  IN  VARCHAR2
                         )
                      IS
                       l_val       NUMBER;
                 BEGIN
                  EXECUTE IMMEDIATE
                     'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO l_val;
                  EXECUTE IMMEDIATE
                     'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY -' || l_val ||
                                                                        ' MINVALUE 0';
                  EXECUTE IMMEDIATE
                     'SELECT ' || p_seq_name || '.NEXTVAL FROM DUAL' INTO l_val;
                  EXECUTE IMMEDIATE
                     'ALTER SEQUENCE ' || p_seq_name || ' INCREMENT BY 1 MINVALUE 0';
                 EXCEPTION
                    WHEN others THEN
                       fnd_file.put_line (fnd_file.log, 'Exception in reset_seq proc: '||sqlerrm);
                       gv_errbuf  := 'Exception in reset_seq proc: '||sqlerrm;
                       gv_retcode := 2;
                 END reset_seq;
              /
              • 4. Re: Dropping index invalidates packages
                BluShadow
                If it's the package state that is being discarded that is causing your error then read the following I wrote ages ago on another thread...


                Packages tend to fail because of their "package state". A package has a "state" when it contains package level variables/constants etc. and the package is called. Upon first calling the package, the "state" is created in memory to hold the values of those variables etc. If an object that the package depends upon e.g. a table is altered in some way e.g. dropped and recreated, then because of the database dependencies, the package takes on an INVALID status. When you next make a call to the package, Oracle looks at the status and sees that it is invalid, then determines that the package has a "state". Because something has altered that the package depended upon, the state is taken as being out of date and is discarded, thus causing the "Package state has been discarded" error message.

                If a package does not have package level variables etc. i.e. the "state" then, taking the same example above, the package takes on an INVALID status, but when you next make a call to the package, Oracle sees it as Invalid, but knows that there is no "state" attached to it, and so is able to recompile the package automatically and then carry on execution without causing any error messages. The only exception here is if the thing that the package was dependant on has changes in such a way that the package cannot compile, in which case you'll get an Invalid package type of error.


                And if you want to know how to prevent discarded package states....

                Move all constants and variables into a stand-alone package spec and reference those from your initial package. Thus when the status of your original package is invlidated for whatever reason, it has no package state and can be recompiled automatically, however the package containing the vars/const will not become invalidated as it has no dependencies, so the state that is in memory for that package will remain and can continue to be used.

                As for having package level cursors, you'll need to make these local to the procedures/functions using them as you won't be able to reference cursors across packages like that (not sure about using REF CURSORS though.... there's one for me to investigate!)



                This first example shows the package state being invalided by the addition of a new column on the table, and causing it to give a "Package state discarded" error...
                SQL> set serveroutput on
                SQL>
                SQL> create table dependonme (x number)
                  2  / 
                 
                Table created.
                 
                SQL>
                SQL> insert into dependonme values (5)
                  2  / 
                 
                1 row created.
                 
                SQL>
                SQL> create or replace package mypkg is
                  2    procedure myproc;
                  3  end mypkg;
                  4  / 
                 
                Package created.
                 
                SQL>
                SQL> create or replace package body mypkg is
                  2    v_statevar number := 5; -- this means my package has a state
                  3
                  4    procedure myproc is
                  5      myval number;
                  6    begin
                  7      select x
                  8      into myval
                  9      from dependonme;
                 10
                 11      myval := myval * v_statevar;
                 12      DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
                 13    end;
                 14  end mypkg;
                 15  / 
                 
                Package body created.
                 
                SQL>
                SQL> exec mypkg.myproc
                My Result is: 25
                 
                PL/SQL procedure successfully completed.
                 
                SQL>
                SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
                  2  / 
                 
                OBJECT_NAME
                --------------------------------------------------------------------------------------------------
                OBJECT_TYPE         STATUS
                ------------------- -------
                MYPKG
                PACKAGE             VALID
                 
                MYPKG
                PACKAGE BODY        VALID
                 
                 
                SQL>
                SQL>
                SQL> alter table dependonme add (y number)
                  2  / 
                 
                Table altered.
                 
                SQL>
                SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
                  2  / 
                 
                OBJECT_NAME
                --------------------------------------------------------------------------------------------------
                OBJECT_TYPE         STATUS
                ------------------- -------
                MYPKG
                PACKAGE             VALID
                 
                MYPKG
                PACKAGE BODY        INVALID
                 
                 
                SQL>
                SQL> exec mypkg.myproc
                BEGIN mypkg.myproc; END;
                 
                *
                ERROR at line 1:
                ORA-04068: existing state of packages has been discarded
                ORA-04061: existing state of package body "SCOTT.MYPKG" has been invalidated
                ORA-06508: PL/SQL: could not find program unit being called: "SCOTT.MYPKG"
                ORA-06512: at line 1
                 
                 
                SQL>
                SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
                  2  / 
                 
                OBJECT_NAME
                --------------------------------------------------------------------------------------------------
                OBJECT_TYPE         STATUS
                ------------------- -------
                MYPKG
                PACKAGE             VALID
                 
                MYPKG
                PACKAGE BODY        INVALID
                 
                 
                SQL>
                SQL> exec mypkg.myproc
                 
                PL/SQL procedure successfully completed.
                 
                SQL>
                SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
                  2  / 
                 
                OBJECT_NAME
                --------------------------------------------------------------------------------------------------
                OBJECT_TYPE         STATUS
                ------------------- -------
                MYPKG
                PACKAGE             VALID
                 
                MYPKG
                PACKAGE BODY        VALID
                And this next example shows how having the package variables in their own package spec, allows the package to automatically recompile when it is called even though it became invalidated by the action of adding a column to the table.
                SQL> drop table dependonme
                  2  / 
                 
                Table dropped.
                 
                SQL>
                SQL> drop package mypkg
                  2  / 
                 
                Package dropped.
                 
                SQL>
                SQL> set serveroutput on
                SQL>
                SQL> create table dependonme (x number)
                  2  / 
                 
                Table created.
                 
                SQL>
                SQL> insert into dependonme values (5)
                  2  / 
                 
                1 row created.
                 
                SQL>
                SQL> create or replace package mypkg is
                  2    procedure myproc;
                  3  end mypkg;
                  4  / 
                 
                Package created.
                 
                SQL>
                SQL> create or replace package mypkg_state is
                  2    v_statevar number := 5; -- package state in seperate package spec
                  3  end mypkg_state;
                  4  / 
                 
                Package created.
                 
                SQL>
                SQL> create or replace package body mypkg is
                  2    -- this package has no state area
                  3
                  4    procedure myproc is
                  5      myval number;
                  6    begin
                  7      select x
                  8      into myval
                  9      from dependonme;
                 10
                 11      myval := myval * mypkg_state.v_statevar;  -- note: references the mypkg_state package
                 12      DBMS_OUTPUT.PUT_LINE('My Result is: '||myval);
                 13    end;
                 14  end mypkg;
                 15  / 
                 
                Package body created.
                 
                SQL>
                SQL> exec mypkg.myproc
                My Result is: 25
                 
                PL/SQL procedure successfully completed.
                 
                SQL>
                SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
                  2  / 
                 
                OBJECT_NAME
                --------------------------------------------------------------------------------------------------
                OBJECT_TYPE         STATUS
                ------------------- -------
                MYPKG
                PACKAGE             VALID
                 
                MYPKG
                PACKAGE BODY        VALID
                 
                 
                SQL>
                SQL> alter table dependonme add (y number)
                  2  / 
                 
                Table altered.
                 
                SQL>
                SQL> select object_name, object_type, status from user_objects where object_name = 'MYPKG'
                  2  / 
                 
                OBJECT_NAME
                --------------------------------------------------------------------------------------------------
                OBJECT_TYPE         STATUS
                ------------------- -------
                MYPKG
                PACKAGE             VALID
                 
                MYPKG
                PACKAGE BODY        INVALID
                 
                 
                SQL>
                SQL> exec mypkg.myproc
                My Result is: 25
                 
                PL/SQL procedure successfully completed.
                • 5. Re: Dropping index invalidates packages
                  769400
                  It's really strange.

                  example:
                  create table test123 (
                    id    number(10) not null 
                  , name1 varchar2(30) not null
                  )
                  /
                  
                  create index test123_i_name on test123 (name1) 
                  /
                  
                  create unique index test123_id on test123(id);
                  
                  alter table TEST123 add constraint TEST123_PK primary key (id);
                  
                  
                  create or replace package test5 as
                  
                    procedure test ( p_name in varchar2 );
                    
                  end;
                  /
                  
                  
                  
                  create or replace package body test5 as
                  
                  g_name test123.name1%type;
                  g_id   test123.id%type;
                  
                  procedure test ( p_name in varchar2 )
                  is
                  begin
                    select id into g_id
                    from test123
                    where name1 = p_name;
                  exception
                    when NO_DATA_FOUND then
                      g_id := null;  
                  end;  
                  
                  end;
                  /
                  In other session I trying to call package TEST5

                  begin
                    TEST5.test('TTT');
                  end;
                  Success execution.


                  After that I trying to drop indexes:
                  drop index TEST123_I_NAME
                  /
                  
                  drop index TEST123_ID
                  /
                  [1]: (Error): ORA-02429: cannot drop index used for enforcement of unique/primary key
                  After that I trying to call package procedure again.

                  Success execution.

                  I dropped constraint to remove index and added it again.
                   
                  alter table TEST123 drop constraint TEST123_PK;
                  alter table TEST123 add constraint TEST123_PK primary key (id) using index;
                  alter table TEST123 drop constraint TEST123_PK;
                  There is no problem with run procedure (in other session) and status of package body isn't changed to INVALID

                  Only if I add column into table
                  alter table TEST123 add name2 varchar2(30) 
                  Running the same script in other session returns error

                  [1]: (Error): ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package body "AEFIMOV.TEST5" has been invalidated ORA-06508: PL/SQL: could not find program unit being called: "AEFIMOV.TEST5" ORA-06512: at line 2

                  My DB version is 10.2.0.4
                  I would ask you to put some examples of your code to understand what does really happen.
                  Could you extract some not very big example, which can reproduce the error.

                  Regards
                  Andrey
                  • 6. Re: Dropping index invalidates packages
                    hurtn
                    Hi all, thanks for your responses but you seem to be missing the key points. Firstly, we are running our own custom plsql ETL which drops indexes, (using execute immediate) loads various tables, and then recreates the index. There are a number of packages involved and as soon as we start dropping indexes the packages become invalid and because everything from one session, these are unable to be invoked. The documenation says that this will occur when views are involved, however we are not using any. I could not believe that this restriction was suddenly implemented in 10g as our ETL logic runs fine on 9.2.
                    Secondly there seemed to be little documentation regarding this common warehouse scenario and as such I have resorted to disabling and rebuilding indexes instead however this is not ideal for a number of reasons, most notibly having to change all indexes to non-unique.
                    • 7. Re: Dropping index invalidates packages
                      996041
                      A work around for the given problem regarding the invalidation of views and PKGs after droping indexes, is the following:

                      - Drop the indexes (as described, execute immediate 'drop index ....')

                      -> Execute a recompile of invalidated objects after droping indexes like:
                      BEGIN
                      for j in(select 'alter '||object_type||' '||object_name||' compile' as rec_stmt from user_objects where status='INVALID') loop
                      execute immediate j.rec_stmt;
                      end loop;
                      END;
                      /

                      - Import your data

                      - rebuild/retsore your indexes execute immediate 'create index ...'
                      • 8. Re: Dropping index invalidates packages
                        John Stegeman
                        Using your first post on OTN to re-open a three-year-old thread doesn't bode well for you :)