• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 18480
  • Last Modified:

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

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
mawingho
Asked:
mawingho
8 Solutions
 
jamesguCommented:
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) from table_name where ...
0
 
mawinghoAuthor Commented:
it doesn't work........
0
 
sdstuberCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
sdstuberCommented:
note, turning auditing on doesn't help with historical timestamps, only as a go-forward solution.
0
 
sdstuberCommented:
oh and scn_to_timestamp doesn't exist in 9i.  it was introduced in 10g.
0
 
NicksonKohCommented:
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
 
sdstuberCommented:
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
 
dbmullenCommented:
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
 
MikeOM_DBACommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now