Home » SQL & PL/SQL » SQL & PL/SQL » How to find out Last Modification Time of a database? (Oracle 12c)
How to find out Last Modification Time of a database? [message #664418] Mon, 17 July 2017 04:44 Go to next message
muniraj_tomar
Messages: 7
Registered: July 2017
Junior Member
Hi all,
we are using below query to get Last Modification Time of a database
select MAX(TIMESTAMP) from ALL_TAB_MODIFICATIONS

But whenever we run this query the output is the same date when this query is executed even though that database is not modified on that date.

We need a QUERY to get the last modified date/time for an oracle database. Any help would be appreciated.
Re: How to find out Last Modification Time of a database? [message #664419 is a reply to message #664418] Mon, 17 July 2017 05:16 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
Is MONITORING on for underlying tables (tables you have privilege)? To test, Gather stats of few tables-> apply DML on these tables -> Flush monitoring Info and check all columns of ALL_TAB_MODIFICATIONS
Re: How to find out Last Modification Time of a database? [message #664420 is a reply to message #664418] Mon, 17 July 2017 05:56 Go to previous messageGo to next message
John Watson
Messages: 7181
Registered: January 2010
Location: Global Village
Senior Member
Your method cannot work. That view is not updated in real time. You would need to execute dbms_stats.flush_database_monitoring_info first. And even then, as the view is cleared by the dbms_stats.gather_% procedures, the information is likely useless.

If you can describe why you want this information, perhaps someone can come up with a technique that will work.

And to do the Moderator bit:
Quote:
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read
Re: How to find out Last Modification Time of a database? [message #664422 is a reply to message #664420] Mon, 17 July 2017 06:12 Go to previous messageGo to next message
muniraj_tomar
Messages: 7
Registered: July 2017
Junior Member
thanks John watson for your quick response . The reason why we want this information is because we need to find out the date/time - an oracle database is last modified, i mean there is no such action on that database since then.
So the problem statement here is that - Is there any way to find out when an oracle database is last modified, if its too long a particular database is not used , we can take necessary action into it?

hope you understand the Use case here.

Thanks in advance.
Re: How to find out Last Modification Time of a database? [message #664423 is a reply to message #664422] Mon, 17 July 2017 06:27 Go to previous messageGo to next message
John Watson
Messages: 7181
Registered: January 2010
Location: Global Village
Senior Member
No, I can't see your usage case. If you want help, you'll need to give a better explanation of what you want to do.
Re: How to find out Last Modification Time of a database? [message #664424 is a reply to message #664422] Mon, 17 July 2017 06:32 Go to previous messageGo to next message
BlackSwan
Messages: 25743
Registered: January 2009
Location: SoCal
Senior Member
muniraj_tomar wrote on Mon, 17 July 2017 04:12
thanks John watson for your quick response . The reason why we want this information is because we need to find out the date/time - an oracle database is last modified, i mean there is no such action on that database since then.
So the problem statement here is that - Is there any way to find out when an oracle database is last modified, if its too long a particular database is not used , we can take necessary action into it?

hope you understand the Use case here.

Thanks in advance.
All DML changes are recorded in REDO log files & details can be obtained using DBMS_LOGMNR.
Re: How to find out Last Modification Time of a database? [message #664425 is a reply to message #664422] Mon, 17 July 2017 06:38 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
You need to define what you mean by a "database modification". With no additional information from you, it could be construed as any of the following, and we don't now which, if any, you have in mind.

- Modification of an initialization parameter
- Addition of a data file
- Resizing of a data file
- Movement of a data file
- Addition or removal of a redo log file group
- Change between archivelog and noarchivelog modes
- Modification to the structure of a table (adding/removing columns)
- Adding or dropping a table
- Adding or dropping an index
- Adding/modifying/dropping a procedure/function/trigger
- modifying data within a table
- loading data from an external source
- adding/modifying/deleting a db link
- <I'm sure others can think of some more>

Re: How to find out Last Modification Time of a database? [message #664426 is a reply to message #664422] Mon, 17 July 2017 06:40 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
muniraj_tomar wrote on Mon, 17 July 2017 06:12
thanks John watson for your quick response . The reason why we want this information is because we need to find out the date/time - an oracle database is last modified, i mean there is no such action on that database since then.
So the problem statement here is that - Is there any way to find out when an oracle database is last modified, if its too long a particular database is not used , we can take necessary action into it?

hope you understand the Use case here.

Thanks in advance.
That didn't answer the question of why you need this information. That just said "we need this information because we want this information." What will you actually DO with the information once you have it? What kind of decision will you make based on the answer? What will you do differently if the "last modified date" (however that is defined) is this morning? A year ago?
Re: How to find out Last Modification Time of a database? [message #664434 is a reply to message #664426] Mon, 17 July 2017 07:32 Go to previous messageGo to next message
Bill B
Messages: 1700
Registered: December 2004
Senior Member
When you say modification I assume that you mean altering objects, not data. If that is true then simply use

select max(last_ddl_time) from dba_objects

this has to be run from a user that can access the dba views
Re: How to find out Last Modification Time of a database? [message #664436 is a reply to message #664422] Mon, 17 July 2017 09:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65249
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
muniraj_tomar wrote on Mon, 17 July 2017 13:12
thanks John watson for your quick response . The reason why we want this information is because we need to find out the date/time - an oracle database is last modified, i mean there is no such action on that database since then.
So the problem statement here is that - Is there any way to find out when an oracle database is last modified, if its too long a particular database is not used , we can take necessary action into it?

hope you understand the Use case here.

Thanks in advance.
An Oracle database is modified about every second, even if you don't do anything Oracle works.

Re: How to find out Last Modification Time of a database? [message #664449 is a reply to message #664418] Tue, 18 July 2017 10:20 Go to previous messageGo to next message
EdStevens
Messages: 863
Registered: September 2013
Senior Member
Looking at all three of your threads, I'm starting to see a pattern, or a common theme ..

http://www.orafaq.com/forum/m/664427/#msg_664427
http://www.orafaq.com/forum/m/664428/#msg_664428

All three appear to be asking a vague question to solve an unknown problem - and quite possibly a non-problem.

Perhaps its time to step back and explain the business issue that is driving all of this.
Re: How to find out Last Modification Time of a database? [message #664653 is a reply to message #664425] Wed, 26 July 2017 06:57 Go to previous messageGo to next message
muniraj_tomar
Messages: 7
Registered: July 2017
Junior Member
We are specifically looking for modification of data within a table. How can we find out last modification date of that?
Re: How to find out Last Modification Time of a database? [message #664655 is a reply to message #664653] Wed, 26 July 2017 07:02 Go to previous messageGo to next message
Deep Chakraborty
Messages: 10
Registered: June 2006
Junior Member
A number of ways you can do it - enable DML audit on that table, else , add a column last_modified in the table and populate the column on row level trigger on the table
Re: How to find out Last Modification Time of a database? [message #664656 is a reply to message #664653] Wed, 26 July 2017 07:03 Go to previous message
BlackSwan
Messages: 25743
Registered: January 2009
Location: SoCal
Senior Member
muniraj_tomar wrote on Wed, 26 July 2017 04:57
We are specifically looking for modification of data within a table. How can we find out last modification date of that?
the table needs a column to contain CHANGE_DATE & a trigger to populate it. with SYSDATE
Previous Topic: Ref cursor results to CSV?
Next Topic: PLS-00201: identifier 'UTL_MAIL' must be declared
Goto Forum:
  


Current Time: Tue Dec 12 10:50:46 CST 2017

Total time taken to generate the page: 0.01541 seconds