This discussion is archived
6 Replies Latest reply: Mar 18, 2013 9:55 AM by John Abate RSS

Views, Procedures, Triggers being marked as invalid

Murray Sobol Newbie
Currently Being Moderated
Oracle 11g 11.2.0.2
Windows 64-bit
Standard Edition

Why are Views, Procedures, Triggers being marked as invalid when there has been NO ACTIVITY against the database?
I can correct them today (by recompiling, no changes are necessary); however tomorrow the same Views, Procedures, Triggers will be marked as invalid.
I can guarantee that there has been NO ACTIVITY in that time frame.

Is there some view I can check to verify that there was no activity in that timeframe?

Thanks
  • 1. Re: Views, Procedures, Triggers being marked as invalid
    sb92075 Guru
    Currently Being Moderated
    Murray Sobol wrote:
    Oracle 11g 11.2.0.2
    Windows 64-bit
    Standard Edition

    Why are Views, Procedures, Triggers being marked as invalid when there has been NO ACTIVITY against the database?
    I can correct them today (by recompiling, no changes are necessary); however tomorrow the same Views, Procedures, Triggers will be marked as invalid.
    I can guarantee that there has been NO ACTIVITY in that time frame.

    Is there some view I can check to verify that there was no activity in that timeframe?

    Thanks
    post results from SQL below
    SELECT owner, 
           object_name, 
           object_type, 
           To_char(last_ddl_time, 'YYYY-MM-DD HH24:MI:SS') 
    FROM   dba_objects 
    WHERE  last_ddl_time > (SELECT Max(last_ddl_time) - 1 
                            FROM   dba_objects) 
  • 2. Re: Views, Procedures, Triggers being marked as invalid
    Murray Sobol Newbie
    Currently Being Moderated
    I modified your query slightly, so I could see the results for the schema owner:
    SELECT *
    FROM   user_objects 
    WHERE  last_ddl_time > (SELECT Max(last_ddl_time) - 1 
                            FROM   user_objects) 
    ;
    
    Here are the results:
    {Code}
    OBJECT_NAME                        SUBOBJECT_NAME   OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE  CREATED                  LAST_DDL_TIME            TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME  
    ---------------------------------- --------------- ---------- -------------- ------------ ------------------------ ------------------------ ------------------- ------- --------- --------- --------- ---------- --------------
    S1_CONTRACT_FORMAT_VIEW                                116264                VIEW         16-JUL-2012 02.13.03 PM  15-MAR-2013 04.28.42 PM  2012-07-16:14:13:03 VALID   N         N         N                  1               
    S1_QUANTITY_ENTRY                                      113211         113211 TABLE        16-JUL-2012 02.01.19 PM  15-MAR-2013 04.30.15 PM  2013-03-15:16:30:15 VALID   N         N         N                  1               
    S1_PLC_DATA                                            113432         113432 TABLE        16-JUL-2012 02.01.51 PM  15-MAR-2013 04.30.30 PM  2013-03-15:16:30:30 VALID   N         N         N                  1               
    FIN_BALANCE_FORWARD_LOCATION                           161145         161145 TABLE        15-MAR-2013 04.27.58 PM  15-MAR-2013 04.28.32 PM  2013-03-15:16:27:58 VALID   N         N         N                  1               
    PK_FIN_BALANCEFORWARD_LOCATION                         161146         161146 INDEX        15-MAR-2013 04.27.58 PM  15-MAR-2013 04.27.58 PM  2013-03-15:16:27:58 VALID   N         N         N                  4               
    FIN_BALANCE_FORWARD_LOCAT_NDX1                         161147         161147 INDEX        15-MAR-2013 04.28.20 PM  15-MAR-2013 04.28.20 PM  2013-03-15:16:28:20 VALID   N         N         N                  4               
    FIN_BALANCE_FORWARD_LOCAT_NDX2                         161148         161148 INDEX        15-MAR-2013 04.28.20 PM  15-MAR-2013 04.28.20 PM  2013-03-15:16:28:20 VALID   N         N         N                  4               
    S1_COMMODITY_TESTER                                    161149         161149 TABLE        15-MAR-2013 04.29.51 PM  15-MAR-2013 04.30.03 PM  2013-03-15:16:29:51 VALID   N         N         N                  1               
    PK_COMMODITY_TESTER                                    161150         161150 INDEX        15-MAR-2013 04.29.51 PM  15-MAR-2013 04.29.51 PM  2013-03-15:16:29:51 VALID   N         N         N                  4               
    
     9 rows selected 
    But, I now have 9 Functions that afre marked as invalid; if I do a recompile they are OK.
    There was NOTHING happening to that database; it was a WEEKEND whn no-one would access it.
  • 3. Re: Views, Procedures, Triggers being marked as invalid
    sb92075 Guru
    Currently Being Moderated
    I can guarantee that there has been NO ACTIVITY in that time frame.
    posted results indicate tables & indexes changed.
    Who changed them & why?
  • 4. Re: Views, Procedures, Triggers being marked as invalid
    Murray Sobol Newbie
    Currently Being Moderated
    That's what I can not determine. No-one had access to that fom Friday at 5:00 PM until this morning at 9:00 AM
  • 5. Re: Views, Procedures, Triggers being marked as invalid
    sb92075 Guru
    Currently Being Moderated
    Murray Sobol wrote:
    That's what I can not determine. No-one had access to that fom Friday at 5:00 PM until this morning at 9:00 AM
    Batch job?
    DBMS_LOGMNR can reveal specific SQL that was executed.
    I can assure you that Oracle RDBMS did NOT spontaneously initiate any SQL.
  • 6. Re: Views, Procedures, Triggers being marked as invalid
    John Abate Newbie
    Currently Being Moderated
    The timestamp of the changes to the indexes and tables says that they were changed at around 4:30. DDL changes to tables will automatically invalidate plsql associated with those tables. Oracle will automatically recompile them the next time they are referenced in most cases. It is Oracle's way of saying "Hey I know the table that this code refers to was changed, I better mark this to be recompiled in case that change broke this code"

Legend

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