Printable View
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.
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.
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.Quote:
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.
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
Developers breathing near the machine?Quote:
Originally posted by Mr.Hanky
As far as I know dropping or altering the base tables, editing the proc will invalidate it, anything else?
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!
Q: Mr.Hanky have u always ended up with "developers" who need to be shot ? Surely there are a few good one out there ?
:p
:D
just curious guys and gals,
thus triggers does not include the lists that affects the invalidity of a procs?:)
Hanky would shoot them in the very first place if he finds such a dev.. Aint it Mr.H? :D :DQuote:
Originally posted by cruser3
Surely there are a few good one out there ?
"The only good ....... is a dead ......." The blanks have been filled in differently over the ages.
Yes, it's possible for a trigger to invalidate a proc: it depends on which direction the gun is pointing at the time.
So can I shoot a developer too who claims that there's no way a trigger can invalidate a procs since it is not part of a table structure and it is not a dependent procs too?:DQuote:
Originally posted by DaPi
"The only good ....... is a dead ......." The blanks have been filled in differently over the ages.
Yes, it's possible for a trigger to invalidate a proc: it depends on which direction the gun is pointing at the time.
Hate to agree with a developer, but I don't see how a trigger modification/drop could affect a procedure or package.Quote:
Originally posted by reydp
So can I shoot a developer too who claims that there's no way a trigger can invalidate a procs since it is not part of a table structure and it is not a dependent procs too?:D
Damn! You guys are out of control today, did they open up a new Starbucks or something?
Quote:
Originally posted by marist89
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.
Ok point taken, it would be removal of the db_link that would invalidate a procedure.
Slim, I think you have taken two rather silly jokes a bit seriously :oQuote:
Originally posted by slimdave
Hate to agree with a developer, but I don't see how a trigger modification/drop could affect a procedure or package.
Hi Dave,Quote:
Originally posted by slimdave
Hate to agree with a developer, but I don't see how a trigger modification/drop could affect a procedure or package.
Now here's what really happened, it took me for a while before I did a conclusion that a trigger can affect a procedure or package.
A developer test her program from the test area and it works fine. The SA/QA give a go signal to put the program on live. proc/tables where also updated. After awhile production users were suddenly calling us, informing that the application that they're using prompted error and that is in relation to the INVALID OBJECTS.
After investigation, one table had discrepancy between test and production. And that is just a single trigger.
Then the trigger where transferred from test to live and boom, previous INVALID OBJECTS becomes validated. We didn't change anything other than that single trigger.
I didn't have time to investigate closer the trigger so I can't give you a concrete evidence regarding this, but one thing I know for sure, is that, that is the only changes we have made after the error, and after changes made to the trigger then it works fine.
regards
One thing,
The test area just don't have a single schema representing the schema used in prod, instead each developer have there own copy of schema. I want to battle this thing out but it may cost my job.
That is an accident waiting to happen. IMO you MUST have something between dev and prod (call it test, QA, UAT, whatever) to server two purposes:Quote:
Originally posted by reydp
The test area just don't have a single schema representing the schema used in prod, instead each developer have there own copy of schema.
1) to test the migration of changes.
2) to provide end-users, QA or whoever, a resonable environment for testing (the chaos in dev is not appropriate for this).
Yes it is.:oQuote:
Originally posted by DaPi
That is an accident waiting to happen. IMO you MUST have something between dev and prod (call it test, QA, UAT, whatever) to server two purposes:
1) to test the migration of changes.
2) to provide end-users, QA or whoever, a resonable environment for testing (the chaos in dev is not appropriate for this).
I already raised this issue to PM/IT Heads. Though it is a valid proposition we always end-up with budget constrain. And they take full responsibility for accident to happen.
In my part, all I can do is make the production server invincible as much as possible. Aside from rman backups, off-site backup server, my standby database is not MANAGED RECOVERY mode so that in a scenario where in an object recovery/data is needed in a certain time, I have something that I can grasp with. Which actually already have happened twice.:D
I don't understand the budget constraint. If all the developers can have a copy of the schema, why not YOU?
Hi Dapi,
We are a company maintaining the I.T. Infrastructures of other companies who also belong to one Group of Companies. We have full control of all the production servers, but we don't provide services for testing/development areas of each company we give services.
Any extra job will cost them and that is why I mentioned "budget constraints" aside from other factors like hardwares if they will be required them to have one.
We try to convince them that there should only be one source of codes(schema/s) before it is put to live, but even this didn't materialized.
:p
regards
If money is so much the issue you can create two instances on the same box -call one test the other dev. And tell the developers that you would keep taps on test and anyone caught loitering in there would lose packing privilges...:p :pQuote:
Originally posted by reydp
Hi Dapi,
We are a company maintaining the I.T. Infrastructures of other companies who also belong to one Group of Companies. We have full control of all the production servers, but we don't provide services for testing/development areas of each company we give services.
Any extra job will cost them and that is why I mentioned "budget constraints" aside from other factors like hardwares if they will be required them to have one.
We try to convince them that there should only be one source of codes(schema/s) before it is put to live, but even this didn't materialized.
:p
regards
I think the point is that "they" are not prepared to pay for testing the process of putting something into production - so "they" pay later.Guessing who "they" might be, you might ask "them" to stand behind and hold any developer that is in Mr.H's sights - could get two birds with one bullet (OK, stone, if you insist).Quote:
Any extra job will cost them
Hi Dapi,Quote:
Originally posted by ndisang
If money is so much the issue you can create two instances on the same box -call one test the other dev. And tell the developers that you would keep taps on test and anyone caught loitering in there would lose packing privilges...:p :p
not possible, current prod server even needs memory and storage for its own needs.:p
Yes, exactly, that is a point where somebody can claim who is PROACTIVE and REACTIVE to problems.:DQuote:
Originally posted by DaPi
I think the point is that "they" are not prepared to pay for testing the process of putting something into production - so "they" pay later.
Guessing who "they" might be, you might ask "them" to stand behind and hold any developer that is in Mr.H's sights - could get two birds with one bullet (OK, stone, if you insist). [/QUOTE]
The QA should also be included in that list. And I can let Mr.H use a GRAND CANNON to fire at "them".
:D :D
Opps I should have address this to SDISANG :)
Quote:
Originally posted by reydp
Hi Dapi,
not possible, current prod server even needs memory and storage for its own needs.:p
Remember that you usually don't need a full-blown server for a Test/QA/UAT machine, since it probably only has to support a couple of users at a time. If you're using Windoze or Linux, have you considered using a PC with a big disk?Quote:
Originally posted by reydp
not possible, current prod server even needs memory and storage for its own needs.:p
Actually, they have test server, as what my previous post claims. It's just a question of how it is being managed and who managed it...me, I only come in when they need me.:pQuote:
Originally posted by DaPi
Remember that you usually don't need a full-blown server for a Test/QA/UAT machine, since it probably only has to support a couple of users at a time. If you're using Windoze or Linux, have you considered using a PC with a big disk?