Home » RDBMS Server » Server Administration » Object change from valid to invalid (Oracle 11g,12c, Linux)
Object change from valid to invalid [message #678123] Tue, 12 November 2019 06:06 Go to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Hi ,

Is there anyway to find when object got changed from valid to invalid in database?

Regards,
Balaji
Re: Object change from valid to invalid [message #678146 is a reply to message #678123] Wed, 13 November 2019 02:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm not sure there is, you can't tell from the *_objects views and if it's not there it's probably not anywhere.
Re: Object change from valid to invalid [message #678147 is a reply to message #678146] Wed, 13 November 2019 03:32 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
I found the select query to find the table name.
select * from user_dependencies where type='VIEW' and reference_name='VIEW_NAME';
Re: Object change from valid to invalid [message #678154 is a reply to message #678147] Wed, 13 November 2019 06:45 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
BalajiDBA wrote on Wed, 13 November 2019 03:32
I found the select query to find the table name.
select * from user_dependencies where type='VIEW' and reference_name='VIEW_NAME';
That's more specific than your original question implied. You asked about any object. (Well, the "any" was implied by not specifiying a particular object type). If you had asked how to find why a view was invalid, you would have gotten better help. But even at that, your question was about "when", not "why", and to that the answer remains the same. There is not built-in data dictionary view that tells when an object became invalid.
Re: Object change from valid to invalid [message #678156 is a reply to message #678154] Wed, 13 November 2019 07:38 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Oh I am sorry I posted wrong query in wrong thread. Kindly ignore it.
Re: Object change from valid to invalid [message #678173 is a reply to message #678156] Thu, 14 November 2019 06:59 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
BalajiDBA wrote on Wed, 13 November 2019 07:38
Oh I am sorry I posted wrong query in wrong thread. Kindly ignore it.
So which part is wrong for this thread? What are we supposed to ignore and what are we supposed to focus on?
Re: Object change from valid to invalid [message #678174 is a reply to message #678173] Thu, 14 November 2019 07:45 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
So which part is wrong for this thread? What are we supposed to ignore and what are we supposed to focus on?Quote:
Kindly reread the post from beginning of this thread to understand better.
Re: Object change from valid to invalid [message #678175 is a reply to message #678174] Thu, 14 November 2019 07:53 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You were the one who posted stuff that you said was in the wrong thread.
So maybe you should explain rather than just telling people to re-read.
Re: Object change from valid to invalid [message #678177 is a reply to message #678123] Thu, 14 November 2019 08:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
BalajiDBA wrote on Tue, 12 November 2019 04:06
Hi ,

Is there anyway to find when object got changed from valid to invalid in database?

Regards,
Balaji

DBMS_LOGMNR can show when object changed from valid to invalid.
Please post solution after your question has been answered.
Re: Object change from valid to invalid [message #678178 is a reply to message #678175] Thu, 14 November 2019 08:20 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Quote:
You were the one who posted stuff that you said was in the wrong thread.
So maybe you should explain rather than just telling people to re-read.
Razz
Re: Object change from valid to invalid [message #678179 is a reply to message #678177] Thu, 14 November 2019 08:24 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Hi Blackswan,
Quote:
Please post solution after your question has been answered.
May I know what solution you are looking from me?
Quote:
DBMS_LOGMNR can show when object changed from valid to invalid.
Can you please explain what is this then?

Regards,
Balaji
Re: Object change from valid to invalid [message #678180 is a reply to message #678179] Thu, 14 November 2019 08:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DBMS_LOGMNR reports content of REDO log file which records all DML changes inside Oracle DB.
When a DB Object goes from valid to invalid it is because DML was applied to the metadata for that object.
So you need to use DBMS_LOGMNR to search when the object was changed. The TIMESTAMP when the object changed is included in the row data.
Re: Object change from valid to invalid [message #678181 is a reply to message #678123] Thu, 14 November 2019 09:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
BalajiDBA wrote on Tue, 12 November 2019 04:06
Hi ,

Is there anyway to find when object got changed from valid to invalid in database?

Regards,
Balaji

Does application as part of normal processing issue DDL via EXECUTE IMMEDIATE?
Re: Object change from valid to invalid [message #678182 is a reply to message #678180] Thu, 14 November 2019 10:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
BlackSwan wrote on Thu, 14 November 2019 14:36
DBMS_LOGMNR reports content of REDO log file which records all DML changes inside Oracle DB.
When a DB Object goes from valid to invalid it is because DML was applied to the metadata for that object.
So you need to use DBMS_LOGMNR to search when the object was changed. The TIMESTAMP when the object changed is included in the row data.
Quite often objects go invalid because DDL was applied to one of the objects referenced by that object, rather than a direct change to that object.
In fact the only way for an object to go invalid due to a direct change is if you run in code that doesn't compile.
Re: Object change from valid to invalid [message #678187 is a reply to message #678180] Thu, 14 November 2019 23:34 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Quote:
DBMS_LOGMNR reports content of REDO log file which records all DML changes inside Oracle DB.
When a DB Object goes from valid to invalid it is because DML was applied to the metadata for that object.
So you need to use DBMS_LOGMNR to search when the object was changed. The TIMESTAMP when the object changed is included in the row data.
Thanks for your explanation.

Quote:
Does application as part of normal processing issue DDL via EXECUTE IMMEDIATE?
I have no idea about this question. It was apps dba who came and asked me whether is there a way to find out when object became invalid.
I told him there is no way to find out when object became invalid.To clarify my doubt,I posted the query here.

Regards,
Balaji
Re: Object change from valid to invalid [message #678188 is a reply to message #678182] Thu, 14 November 2019 23:36 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Quote:
Quite often objects go invalid because DDL was applied to one of the objects referenced by that object, rather than a direct change to that object.In fact the only way for an object to go invalid due to a direct change is if you run in code that doesn't compile.
Thank you for your explanation.
Re: Object change from valid to invalid [message #678190 is a reply to message #678179] Fri, 15 November 2019 07:13 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
BalajiDBA wrote on Thu, 14 November 2019 08:24
Hi Blackswan,
Quote:
Please post solution after your question has been answered.
May I know what solution you are looking from me?
Quote:
DBMS_LOGMNR can show when object changed from valid to invalid.
Can you please explain what is this then?

Regards,
Balaji
What did you discover for yourself when you googled "dbms_logmnr"?
Re: Object change from valid to invalid [message #678191 is a reply to message #678187] Fri, 15 November 2019 07:15 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
BalajiDBA wrote on Thu, 14 November 2019 23:34
Quote:
DBMS_LOGMNR reports content of REDO log file which records all DML changes inside Oracle DB.
When a DB Object goes from valid to invalid it is because DML was applied to the metadata for that object.
So you need to use DBMS_LOGMNR to search when the object was changed. The TIMESTAMP when the object changed is included in the row data.
Thanks for your explanation.

Quote:
Does application as part of normal processing issue DDL via EXECUTE IMMEDIATE?
I have no idea about this question. It was apps dba who came and asked me whether is there a way to find out when object became invalid.
I told him there is no way to find out when object became invalid.To clarify my doubt,I posted the query here.

Regards,
Balaji
So why didn't you pose the question to the "apps dBA", or the application developers? Working these kinds of issues within the organization should be a two-way street.
Previous Topic: Blank username in v$session , How to find who is connecting
Next Topic: Plenty of UNDO but keep getting ORA-01555
Goto Forum:
  


Current Time: Thu Mar 28 04:36:25 CDT 2024