OraFAQ Forum: SQL & PL/SQL » How to find out Last Modification Time of a database?

Home » SQL & PL/SQL » SQL & PL/SQL » How to find out Last Modification Time of a database? (Oracle 12c)

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

How to find out Last Modification Time of a database? [message #664418]

Mon, 17 July 2017 04:44

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.

Report message to a moderator

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

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

Report message to a moderator

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

John Watson
Messages: 8929
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

Report message to a moderator

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

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.

Report message to a moderator

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

John Watson
Messages: 8929
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.

Report message to a moderator

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

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

EdStevens
Messages: 1376
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>

Report message to a moderator

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

EdStevens
Messages: 1376
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?

Report message to a moderator

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

Bill B
Messages: 1971
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

Report message to a moderator

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

Michel Cadot
Messages: 68641
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.

Report message to a moderator

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

EdStevens
Messages: 1376
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.

Report message to a moderator

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

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?

Report message to a moderator

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

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

Report message to a moderator

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

BlackSwan
Messages: 26766
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

Report message to a moderator

Previous Topic:	Ref cursor results to CSV?
Next Topic:	PLS-00201: identifier 'UTL_MAIL' must be declared

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 04:40:27 CDT 2024