?
Solved

Find out when the last insert in a table occured

Posted on 2011-05-13
12
Medium Priority
?
1,019 Views
Last Modified: 2012-05-11
How can I find out the last time rows were inserted in a table.
The db is Oracle 9i
0
Comment
Question by:diteps06
  • 3
  • 3
  • 3
  • +2
12 Comments
 
LVL 23

Assisted Solution

by:OP_Zaharin
OP_Zaharin earned 240 total points
ID: 35752764
- if you have a lastdateupdate column which you insert the date and time of a record inserted, then you can use MAX() function on that column.

SELECT MAX(datecolumn) FROM tablename
0
 
LVL 1

Author Comment

by:diteps06
ID: 35752804
Unfortunately there isn't a timestamp column which records when the insert statement occured
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 35752805
Directly not.
Imlicitell you can see the last SCN:

SELECT max( ORA_ROWSCN) FROM my_table;

Example:

SQL> SELECT max( ORA_ROWSCN) FROM LOCAL_MEMBERS;

MAX(ORA_ROWSCN)
---------------
        4128753

 Now

select scn_to_timestamp(4128753) as timestamp from dual;

or

SELECT TO_CHAR(TIME_DP,'DDMONYYYY HH24:MI') DATE_TIME,SCN
FROM SMON_SCN_TIME;

Some details here:

http://wiki.oracle.com/page/SCN

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35752860
- basically you need a timestamp column such as lastedateupdate from the above example to get the last insert record in a table. you need to create a column for such and then use either a trigger or set default SYSDATE value for that column.

- even by using ORA_ROWSCN, there is a possibility that you might not getting a confirmed max row. from what i understand, its  depending on how do you specify the maintenance of ORA_ROWSCN during the table creation, at the row level or defaulted at block level (no row dependency). if not specified, it is possible that there is duplicate ORA_ROWSCN for a block of record. i would suggest to verify its uniqueness first.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35753327
if you don't have some sort of auditting built in, you can't do what you are trying.

the ora_rowscn  is only an approximation, as the SCN is associated with a transaction, not a specific statement.

also, the data isn't around forever so scn_to_timestamp will fail with "ORA-08181: specified number is not a valid system change number" if old enough
0
 
LVL 1

Author Comment

by:diteps06
ID: 35753816
The function ORA_ROWSCN is for oracle 10 not 9i. I
My db is Oracle 9 as stated in the question
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 320 total points
ID: 35753879
and as mentioned, it's only an approximation anyway.

to track times, you will have to use some auditting

either a trigger you write yourself, or oracle built in auditting, or prevening direct access to your tables and forcing all writes through procedures/functions that will log the activity.


I recommend writing something yourself because, even though it's more work than letting Oracle track it for you,  your auditting procedure will be able to provide some business context "closing an order"  'receiving inventory"  etc  which is more useful than simply source and time
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35753893
another "limited duration" option is flashback queries which are supported in 9i

but the best way will be to write some auditting
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35754415
Is this a one time deal to see if a table is actually being used or something you need moving forward?

If a one time deal and if you are in archive log mode you might see if log miner will help.
0
 
LVL 1

Author Comment

by:diteps06
ID: 35755202
It's a one time deal
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 240 total points
ID: 35758154
Look into log miner.  Go through all your existing archived logs.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35758200
Is this to see if a table is actually used?

If so, check the dba_dependicies view first.

It's risky but:
If there aren't any and you are really interested, just rename it and see who complains or what breaks.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

571 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