Home » SQL & PL/SQL » SQL & PL/SQL » Record insertion time (10.2.0.3.0)
Record insertion time [message #414984] Fri, 24 July 2009 05:25 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi,

I have a table with some records. I would like to know when each record was inserted (I don't have a "date" column in the table for each record).
Is it possible? Does oracle have a feature like that?

Please let me know.

Thank you,
SNN
Re: Record insertion time [message #414985 is a reply to message #414984] Fri, 24 July 2009 05:27 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
No.

By
Vamsi
Re: Record insertion time [message #414986 is a reply to message #414985] Fri, 24 July 2009 05:31 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Vamsi,

can you please let me know how to find out if required?

Thank you,
SNN
Re: Record insertion time [message #414988 is a reply to message #414986] Fri, 24 July 2009 05:37 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') from dual


you can insert this(at time of insert Smile ) in your character field as well but it is more performance efficient if you store in a date field only.you can search the forum if you want to know why?

[Updated on: Fri, 24 July 2009 05:44]

Report message to a moderator

Re: Record insertion time [message #414990 is a reply to message #414988] Fri, 24 July 2009 05:56 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
./fa/449/0/

Another "Laugh Of The Day" dialogue:

- Is it possible?
- No
- Is it possible if it's required?
Re: Record insertion time [message #414991 is a reply to message #414984] Fri, 24 July 2009 05:57 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> create table test
  2  ( i varchar2(40));

Table created.

SQL> insert into test values('ayush');

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT last_active_time, sql_text
  2    FROM v$sqlarea
  3   WHERE INSTR (sql_text, 'insert into test values') > 0;

LAST_ACTI SQL_TEXT
--------- ------------------------------------------------------------------------------------------
24-JUL-09 SELECT last_active_time    FROM   v$sqlarea  where instr(sql_text,'insert into test values
24-JUL-09 SELECT last_active_time,sql_text    FROM   v$sqlarea  where instr(sql_text,'insert into tes
24-JUL-09 insert into test values('ayush')
24-JUL-09 SELECT last_active_time, sql_text   FROM v$sqlarea  WHERE INSTR (sql_text, 'insert into te

[Updated on: Fri, 24 July 2009 05:57]

Report message to a moderator

Re: Record insertion time [message #414996 is a reply to message #414991] Fri, 24 July 2009 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's not really what the OP is looking for.
1) That's all dynamic data and will be gone soon
2) It can't cope with bind variables
3) It can't actually tell you which row that is - if there are 3 rows with the same value, it tells you nothing about which row it is refering to.
4) It can't cope with procedural code inserting into the table.

It is, however, a good piece of lateral thinking.
Re: Record insertion time [message #415026 is a reply to message #414986] Fri, 24 July 2009 07:45 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
user71408 wrote on Fri, 24 July 2009 06:31
Vamsi,
can you please let me know how to find out if required?



Vamsi, you better look into this!!!
Re: Record insertion time [message #415036 is a reply to message #415026] Fri, 24 July 2009 07:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Vamsi, you better look into this!!!
Do you think he responds to my posts only? Razz
Quote:
Vamsi,
can you please let me know how to find out if required?
My answer:
1. That's why we use WHO columns in most of the applications
(creation_date,created_by,last_updated_by,last_update_date,last_update_login).
2. If you want to know, you have to have some columns to keep track of the time. No other go.

By
Vamsi

[Updated on: Fri, 24 July 2009 08:01]

Report message to a moderator

Re: Record insertion time [message #415038 is a reply to message #415036] Fri, 24 July 2009 08:03 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I think it is about time we start feeding this shleprock false information. He doesn't seem to comprehend anything we say, so we may as well just start giving outlandish responses until he goes away.
Re: Record insertion time [message #415061 is a reply to message #415038] Fri, 24 July 2009 10:15 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I understand what you mean, after reading the post "Suggestion for a new forum" in Moderator Discussions Forum.

By
Vamsi
Previous Topic: 2 FULL Table Access for the update statement
Next Topic: finding entries with an return on the end
Goto Forum:
  


Current Time: Mon Dec 05 13:21:20 CST 2016

Total time taken to generate the page: 0.18383 seconds