OraFAQ Forum: Server Administration » Object change from valid to invalid

Home » RDBMS Server » Server Administration » Object change from valid to invalid (Oracle 11g,12c, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Object change from valid to invalid [message #678123]

Tue, 12 November 2019 06:06

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

Report message to a moderator

Re: Object change from valid to invalid [message #678146 is a reply to message #678123]

Wed, 13 November 2019 02:54

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: Object change from valid to invalid [message #678147 is a reply to message #678146]

Wed, 13 November 2019 03:32

BalajiDBA
Messages: 139
Registered: October 2018
Location: India

Senior Member

I found the select query to find the table name.

Code: [Select all] [Show/ hide]

select * from user_dependencies where type='VIEW' and reference_name='VIEW_NAME';

Report message to a moderator

Re: Object change from valid to invalid [message #678154 is a reply to message #678147]

Wed, 13 November 2019 06:45

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.
Code: [Select all] [Show/ hide]
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.

Report message to a moderator

Re: Object change from valid to invalid [message #678156 is a reply to message #678154]

Wed, 13 November 2019 07:38

BalajiDBA
Messages: 139
Registered: October 2018
Location: India

Senior Member

Oh I am sorry I posted wrong query in wrong thread. Kindly ignore it.

Report message to a moderator

Re: Object change from valid to invalid [message #678173 is a reply to message #678156]

Thu, 14 November 2019 06:59

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?

Report message to a moderator

Re: Object change from valid to invalid [message #678174 is a reply to message #678173]

Thu, 14 November 2019 07:45

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.

Report message to a moderator

Re: Object change from valid to invalid [message #678175 is a reply to message #678174]

Thu, 14 November 2019 07:53

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: Object change from valid to invalid [message #678177 is a reply to message #678123]

Thu, 14 November 2019 08:13

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.

Report message to a moderator

Re: Object change from valid to invalid [message #678178 is a reply to message #678175]

Thu, 14 November 2019 08:20

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.

Report message to a moderator

Re: Object change from valid to invalid [message #678179 is a reply to message #678177]

Thu, 14 November 2019 08:24

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

Report message to a moderator

Re: Object change from valid to invalid [message #678180 is a reply to message #678179]

Thu, 14 November 2019 08:36

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.

Report message to a moderator

Re: Object change from valid to invalid [message #678181 is a reply to message #678123]

Thu, 14 November 2019 09:34

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?

Report message to a moderator

Re: Object change from valid to invalid [message #678182 is a reply to message #678180]

Thu, 14 November 2019 10:10

cookiemonster
Messages: 13966
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.

Report message to a moderator

Re: Object change from valid to invalid [message #678187 is a reply to message #678180]

Thu, 14 November 2019 23:34

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

Report message to a moderator

Re: Object change from valid to invalid [message #678188 is a reply to message #678182]

Thu, 14 November 2019 23:36

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.

Report message to a moderator

Re: Object change from valid to invalid [message #678190 is a reply to message #678179]

Fri, 15 November 2019 07:13

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"?

Report message to a moderator

Re: Object change from valid to invalid [message #678191 is a reply to message #678187]

Fri, 15 November 2019 07:15

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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 14 07:10:05 CDT 2025