Solved

How to find the last update time of each table in Oracle?

Posted on 2008-10-08
9
14,099 Views
Last Modified: 2013-12-19
I have a program that link to a database, however, when I use the program, I want to know which table does it updated? However, this program does not write any timing information nor the tables do not have a column that store the last update time. Therefore, I would like to know does Oracle records the last alteration / update time of each table? If not, are there any other methods that let me find out the last update time? Thanks!
0
Comment
Question by:mawingho
9 Comments
 
LVL 9

Assisted Solution

by:jamesgu
jamesgu earned 50 total points
ID: 22675197
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) from table_name where ...
0
 

Author Comment

by:mawingho
ID: 22675219
it doesn't work........
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 22675234
How do you define "last update time"?




the ora_rowscn suggested above shows when the commit happened,  not necessarily when the updates occured.

also note,  if the last alteration to a table was a delete, then you don't have a row for the ora_rowscn.

without logs in your application, row level auditing will give you what you want,  after that best you could do would be to go through log miner for scn estimate times.


0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 22675237
note, turning auditing on doesn't help with historical timestamps, only as a go-forward solution.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 22675249
oh and scn_to_timestamp doesn't exist in 9i.  it was introduced in 10g.
0
 
LVL 17

Assisted Solution

by:NicksonKoh
NicksonKoh earned 50 total points
ID: 22675557
If you use TOAD, you can easily see the last DDL date on the table.
Just go to Schema Browser and click on the Tablename. You can see the table creation and last DDL information just above the tabs.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 22675806
last_ddl_time is when the table was last changed "structurally"  like truncate, create, alter table, etc.

It doesn't account for DML changes:  insert, update, delete
0
 
LVL 10

Assisted Solution

by:dbmullen
dbmullen earned 50 total points
ID: 22678254
it appears you're trying to "reverse engineer" some process

turn on tracing
run the program
turn off tracking
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sqltrace.htm#1018

look at the dump file.
that will show everything that program does.

if tracing isn't an option, you'll have to create an after insert or update or delete trigger on every table..

forget about trying to find what happened already.  just do it again.

0
 
LVL 29

Accepted Solution

by:
MikeOM_DBA earned 150 total points
ID: 22678311
Otherwise,
If you have "monitoring" option set, just check:

{ALL|DBA|USER}_TAB_MODIFICATIONS

SQL> desc ALL_TAB_MODIFICATIONS

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 TABLE_OWNER                                        VARCHAR2(30)

 TABLE_NAME                                         VARCHAR2(30)

 PARTITION_NAME                                     VARCHAR2(30)

 SUBPARTITION_NAME                                  VARCHAR2(30)

 INSERTS                                            NUMBER

 UPDATES                                            NUMBER

 DELETES                                            NUMBER

 TIMESTAMP                                          DATE

 TRUNCATED                                          VARCHAR2(3)

 DROP_SEGMENTS                                      NUMBER

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to create a delete query to delete entire records 2 33
Oracle Next Available Number 2 32
How to connect SQL Server from my Oracle database? 11 72
Queries 15 34
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now