This discussion is archived
1 Reply Latest reply: Nov 30, 2012 1:06 AM by stratmo RSS

User_objects clarification

879380 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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:"

Legend

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