DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 29

Thread: What would invalidate a procedure?

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    3,134

    What would invalidate a procedure?

    Sorry to speak in such broad strokes but this is a question that was asked today. We had a download procedure go invalid today.

    As far as I know dropping or altering the base tables, editing the proc will invalidate it, anything else?

    A network glitch should not cause it to go invalid.
    The proc basically downloads data through a db_link from another database.
    I remember when this place was cool.

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    a procedure that it calls gets modified and it will go invalid. Check for all objects that had a last_ddl_Time of the day it went invalid for a starting point. You can also query sys.dependancy$ to check dependancies for that object.
    Jeff Hunter

  3. #3
    Join Date
    Jul 2002
    Posts
    335
    Surely a network glitch could invalidate an object. If that object in database x was in some connected via a db link to another object in database y, and the network between the two went down.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by bazza
    Surely a network glitch could invalidate an object. If that object in database x was in some connected via a db link to another object in database y, and the network between the two went down.
    That sounds more like a guess. If that were the case whenever a remote database shutdown it would invalidate every object that depends on it. This is definitely not the case.
    Last edited by marist89; 09-22-2004 at 12:25 PM.
    Jeff Hunter

  5. #5
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    clear breaks
    A network glitch would NOT invalidate dependent objects ... If so,
    Look at Oracle Replication ... It would be a nightmare...

    Here's a little script to find the dependences ...

    break on r_name skip 1
    column r_name format a80
    column name format a80
    column r_link format a80
    set heading off

    select decode(referenced_type, 'NON-EXISTENT', '.....', referenced_type)
    || ' ' || referenced_owner || '.' || referenced_name r_name,
    ' is referenced by: ' || type || ' ' || owner || '.' || name name,
    ' Referenced Link: ' || decode(referenced_link_name, null, 'none',
    referenced_link_name) r_link
    from sys.dba_dependencies
    where referenced_name = '&&table_name'
    order by 1,2;

    HTH
    Gregg

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253

    Re: What would invalidate a procedure?

    Originally posted by Mr.Hanky
    As far as I know dropping or altering the base tables, editing the proc will invalidate it, anything else?
    Developers breathing near the machine?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jan 2001
    Posts
    3,134
    Thanks for the replies guys, another suggestion from one of our developers was that someone created a TEMP table with the same name as an existing table and then dropped it. This is possible but no one is fessing up, big surprise. Even the developers want to shoot the developer responsible.

    Thanks for the query Jeff, I checked on all object with DDL against them. We still think it was a temp table, we NEVER should have given them schemas that allow them to create tables in production.

    C'mon, everyone sing along with me..........

    "Shoot the developers!"
    "Shoot the developers!"
    "Shoot the developers!"

    Roses are nice
    Violets are mute
    I never met a devloper
    I didn't wanna SHOOT!
    I remember when this place was cool.

  8. #8
    Join Date
    Jul 2003
    Posts
    323
    Q: Mr.Hanky have u always ended up with "developers" who need to be shot ? Surely there are a few good one out there ?



  9. #9
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    just curious guys and gals,
    thus triggers does not include the lists that affects the invalidity of a procs?

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by cruser3
    Surely there are a few good one out there ?
    Hanky would shoot them in the very first place if he finds such a dev.. Aint it Mr.H?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

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


Click Here to Expand Forum to Full Width