1 Reply Latest reply: Nov 30, 2012 3:06 AM by stratmo RSS

    User_objects clarification

    879380
      hi,

      We are facing a issue in 11g R2 version when we are executing the view scripts some packages are getting decompiled status . Not sure that its related to any DBA issue . but while analysis the user_objects table suddenly i got stuck in the last_ddl_time column.

      Consider i am creating a packages
      CREATE OR REPLACE
      PACKAGE PACKAGE1
      AS
      PROCEDURE prc_test ;
      END PACKAGE1;
      CREATE OR REPLACE
      PACKAGE body PACKAGE1
      AS
      PROCEDURE prc_test
        BEGIN
          NULL;
        END;
      END;
      while querying the user_objects the last_ddl_time column will show the dll time
      select * from user_objects where object_name ='PACKAGE1';
      
      result:
      PACKAGE1     30-NOV-12
      PACKAGE1     30-NOV-12
      now i am doing a dll table change tomorrow and executing the DBMS_UTILITY.compile_schema (l_vc_user); compile scripts
      since the DBMS_UTILITY.compile_schema is done then the date should should show tomorrow right


      Please clarify why the date is not changing . Plus give me hint for finding why packages are getting de compiled for executing the view ddl scripts is there any thing done for tracking ti
        • 1. Re: User_objects clarification
          stratmo
          Hi,

          please try this. It should clarify your first question. the date will change please believe me. If my example is wrong please give me feedback.
          CREATE OR REPLACE
          PACKAGE PACKAGE1
          AS
          PROCEDURE prc_test ;
          END PACKAGE1;
          /
          
          CREATE OR REPLACE
          PACKAGE body PACKAGE1
          AS
          PROCEDURE prc_test
          as
            BEGIN
              NULL;
            END;
          END;
          /
          
          drop table obj_ddl_test;
          create table obj_ddl_test
          as
          select 1 as run_id, to_char(o.last_ddl_time,'YYYYMMDD HH24:MI:SS') last_ddl_time, o.object_name, o.object_type
          from user_objects o
          where o.object_name = 'PACKAGE1';
          
          begin
             dbms_lock.sleep(10);
             dbms_utility.compile_schema('OTN');
          end;
          /
          
          insert into obj_ddl_test
          select 2 as run_id, to_char(o.last_ddl_time,'YYYYMMDD HH24:MI:SS') last_ddl_time, o.object_name, o.object_type
          from user_objects o
          where o.object_name = 'PACKAGE1';
          
          select * from obj_ddl_test
          where object_name = 'PACKAGE1'
          order by 1,2,3,4;
          Bye

          stratmo

          PS:
          1. COMPILE_SCHEMA changes the LAST_DDL_TIME (see above)
          2. If "decompiled" means invalid, then take a look at the data-dictionary-views concerning dependencies.
          Very short example:
          select usr.username username, usr.user_id, usr.created usr_creation, deps.*
          from user_dependencies deps
             , all_users usr
          where usr.username = 'OTN'
          and deps.schemaid = usr.user_id
          and deps.referenced_owner ='OTN'
          ;
          Along these dependencies objects will get invalidated.

          Edited by: stratmo on Nov 30, 2012 10:02 AM
          added "PS:"