Skip to Main Content
  • Questions

  • How to capture the oldest rows and their creation date

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Renee.

Asked: January 09, 2020 - 1:41 am UTC

Last updated: January 10, 2020 - 3:44 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

I am trying to put together a report that will show old data that may be candidates for deletion.

Will you please let me know how to capture the oldest creation date of rows (along with the row) in all non-system tables?

We are running Oracle version 12.2.0.1.

Thank you

and Chris said...

One of the easiest ways is to have an insert date column that defaults to sysdate/systimestamp:

create table t (
  c1 int,
  ins_ts timestamp 
    default systimestamp
);

insert into t values ( 1, default );
exec dbms_lock.sleep ( 7 );
insert into t values ( 2, default );

select * from t;

C1    INS_TS                           
    1 09-JAN-2020 10.43.08.806278000    
    2 09-JAN-2020 10.43.16.469880000


If you're trying to discover the oldest rows on existing tables that don't have something this... you're likely out of luck.

ORA_ROWSCN gets you close, but has lots of issues which makes it unreliable:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9532351800346655576
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:517105100346104196

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ALOK Verma, January 09, 2020 - 12:50 pm UTC

Dear Sir,

Interviewer ask me this question, How to solve this problem through sql ?

TASKID | EMPLOYEECODE| SLOT_ACTION | date

1 A S 08/jan/2020 09:30:32 am
1 A p 08/jan/2020 02:10:20 pm
1 A S 09/jan/2020 09:50:50 am
1 A P 09/jan/2020 12/50:58 pm
1 A S 10/jan/2020 09:30:32 am
1 A P 10/jan/2020 12/50:58 pm
1 A S 11/jan/2020 09:30:32 am
1 A p 11/jan/2020 02:10:20 pm
1 A S 12/jan/2020 09:50:50 am
1 A P 12/jan/2020 12/50:58 pm
1 A S 13/jan/2020 09:30:32 am
1 A P 13/jan/2020 12/50:58 pm
1 A S 14/jan/2020 09:30:32 am
1 A p 14/jan/2020 02:10:20 pm
1 A S 15/jan/2020 09:50:50 am
1 A P 15/jan/2020 12/50:58 pm
1 A S 16/jan/2020 09:30:32 am
1 A P 16/jan/2020 12/50:58 pm
1 A S 17/jan/2020 09:30:32 am
1 A p 17/jan/2020 02:10:20 pm
1 A S 18/jan/2020 09:50:50 am
1 A P 18/jan/2020 12/50:58 pm
1 A S 19/jan/2020 09:30:32 am
1 A P 19/jan/2020 12/50:58 pm
. . . .
. . . .
like that 100 records there
2nd last and last record is

1 A S 10/feb/2020 09:30:58 am
1 A E 10/feb/2020 05:20:25 pm

they want calculate time.
they ask how much time taken to complete takid 1 for employee A?

Where
S - Start work
P - Pause work
E - End or completed work.
1 - Taskid
A - EMPLOYEECODE
Chris Saxon
January 09, 2020 - 1:20 pm UTC

You'll need to sum up the time between each (S,P) pair and the final S + E...

...but what's this got to do with the original question?

Excellent and fast assistance

Renee Bell, January 10, 2020 - 1:52 am UTC

Thank you very much for your response to my original question. It helped greatly. By the way, I have no idea why the other person asked that question that has no correlation to my question.
Connor McDonald
January 10, 2020 - 3:44 am UTC

Welcome to the world of AskTOM :-) We have no idea either.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.