[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

to find when the first row was inserted into the table

I am looking to find when(day and time) the first record was inserted into a table(say xyz)..please let me know if there is any dictionary table(s) storing this information..

thanks..
0
gs79
Asked:
gs79
  • 4
  • 3
  • 2
  • +3
4 Solutions
 
htonkovCommented:
Hi!
There is no dictionary table with such data.
You can make a trigger which would handle that, or you can search through archive logs (if you have long enough archive of them).
<brainstorming>Maybe you could search through historical statistics data, but that would get only approximate result</brainstorming>

If you need it for forensic purposes (you already need that information), I'd try archivelogs. If you need it for the future - I'd use trigger.

Regards
Hrvoje
0
 
slightwv (䄆 Netminder) Commented:
I agree with the above post about the past.  Moving forward I wanted to throw out auditing as well.
0
 
ajexpertCommented:
What is your table structure?  I believe you might NOT have any column which maintains the log when the record was created.

Does your table have primary key that has sequence?

The record with least sequence number, could be the first record.

There are lot of hassels techincally to find out the first record if any of above is not true.

Agree with htonkov that you need to understand business and find out logically what could be the first record.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
sdstuberCommented:
You could try this as an approximation


select t.*, scn_to_timestamp(ora_rowscn) x from yourtable t order by x


but if your data is too old then you might get ORA-08181 errors
0
 
gs79Author Commented:
Ajexpert..

I think I am at a bit of luck here. the table is a fact table with a sequence as a primary key as it is a fact table..how to find the first record inserted in that case..

Thanks
0
 
sdstuberCommented:
select * from
(select * from yourtable order by your_sequence )
where rownum = 1
0
 
sdstuberCommented:
that won't tell you "when" the row was inserted, but it will give you the smallest sequence value.  Which is "probably" the first row inserted.

I say probably because it's not guaranteed.

for instance.

I insert  id 1
you insert id 2
you commit
I commit

your row is in the database first with id 2
my row is in the database later with id 1
0
 
sdstuberCommented:
another hole

I pull the sequence value 1
you pull the sequence value 2

you insert your id
you commit

i insert my id
i commit

again,  your data is in first with id 2
my data is in second with id 1
0
 
htonkovCommented:
gs79,

do you use this sequence only for that table or maybe for some other also? If you use the sequence for other tables, and if these tables do have timestamp column (like date created), you might approximately know time range when the insert occured.
(I use the same sequence for practically everything, so It's easy to guess, of cource one should take care about sequence caching)

Regards
0
 
sventhanCommented:
try ora_rowscn and SCN_TO_TIMESTAMP to convert to the time...


select * from yourtable order by your_sequence

SELECT ora_rowscn, columns from yourtable order by your_sequence desc

http://wiki.oracle.com/page/SCN
0
 
slightwv (䄆 Netminder) Commented:
>>try ora_rowscn and SCN_TO_TIMESTAMP to convert to the time...


I believe that has already been mentioned in http:#a35511835
0
 
sventhanCommented:
Thanks SlightVW.  I did not notice.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now