Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2008
    Posts
    14

    Unanswered: How to get the date and time when a database object got INVALID

    Hi all,

    Would like to ask if there is a way to determine the actual time when a particular database object got INVALID in an ORACLE database.

    Thanks and regards,

  2. #2
    Join Date
    Mar 2008
    Posts
    89
    I'd like to know that too actually.

    I would think you can use the timestamp column in dba_objects:

    Code:
    select status, timestamp from dba_objects 
    where object_name='SEND_HANS_TO_BED'
      and owner='MAMA';
    ... which records the latest change to that object?

    But that's just my first guess.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    That doesn't work, I'm afraid. I don't know if there is any way to do this - maybe an entry gets written to the log file?

  4. #4
    Join Date
    Mar 2008
    Posts
    89
    How about a trigger on the depending tables to write that info into a user-defined audit table?
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There isn't a trigger type "AFTER INVALIDATION OF <table>" unfortunately!

  6. #6
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by andrewst
    There isn't a trigger type "AFTER INVALIDATION OF <table>" unfortunately!
    I was thinking more of any DDL trigger changing that table that the other object depends on.

    Maybe it could check if the dependant objects are valid first, then execute the triggering DDL, and afterwards check if the object status has changed to INVALID... and only then record the timestamp, and maybe the causing DDL and user in a separate table...?

    I know I'm leaning far out here... But there should be a way to get this information.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I see where you are coming from, but it does sound a lot of work. Maybe instead just have a batch job that runs periodically and checks for invalid objects, and logs them to another table with a timestamp?

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Depending on...

    Maybe you could check the timestamp on the objects on which the particular object is a dependant?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •