Home » SQL & PL/SQL » SQL & PL/SQL » how to fetch last inserted record from oracle table?
how to fetch last inserted record from oracle table? [message #108629] Fri, 02 April 2004 01:25 Go to next message
anupam sao
Messages: 2
Registered: April 2004
Junior Member
how to fetch last inserted record from oracle table?
Re: how to fetch last inserted record from oracle table? [message #108638 is a reply to message #108629] Mon, 19 April 2004 03:18 Go to previous messageGo to next message
fanni
Messages: 96
Registered: March 2004
Member
select * from user_registration where rowid = (select max(rowid) from user_registration);
Re: how to fetch last inserted record from oracle table? [message #332659 is a reply to message #108638] Wed, 09 July 2008 05:33 Go to previous messageGo to next message
anupam sao1
Messages: 2
Registered: June 2008
Location: Bangalore
Junior Member
Thanks Dude
Re: how to fetch last inserted record from oracle table? [message #332686 is a reply to message #108638] Wed, 09 July 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong.
There is no direct way unless you have a timestamp column.
Otherwise you have to search in archive, undo or redo area.

Regards
Michel
Re: how to fetch last inserted record from oracle table? [message #332985 is a reply to message #332686] Thu, 10 July 2008 04:30 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Wed, 09 July 2008 06:37
This is wrong.



May I know why? I would be thankful to you if you explain why.

Regards,
Oli
Re: how to fetch last inserted record from oracle table? [message #332988 is a reply to message #332985] Thu, 10 July 2008 04:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Because a new column might be inserted with a ROWID lower than an already existing old one.

You can assume that the chances of a new row getting a higher rowid than the old ones in the table are somewhat higher than getting a lower one (especially when there have NEVER been any deletions from the table) but you can NOT depend on it.



Re: how to fetch last inserted record from oracle table? [message #332989 is a reply to message #332985] Thu, 10 July 2008 04:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because there is no concept of 'last' in a relational database.

Unless you have an ORDER BY clause, there is no guarantee what order you will get the records back in.
FOr small tables that don't use an index (ie almost all demo tables in this forum) rows will almost invariably come back in the order that they were inserted in, as that is the order that a Full Table Scan will read them in.

But, in a live system where you delete rows, you will get rows inserted to fill in the gaps in blocks left by earlier deletions. This means that the highest rowid does not have to refer to the last inserted record.
Re: how to fetch last inserted record from oracle table? [message #332995 is a reply to message #332989] Thu, 10 July 2008 04:45 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Thu, 10 July 2008 04:39
Because there is no concept of 'last' in a relational database.

Unless you have an ORDER BY clause, there is no guarantee what order you will get the records back in.
FOr small tables that don't use an index (ie almost all demo tables in this forum) rows will almost invariably come back in the order that they were inserted in, as that is the order that a Full Table Scan will read them in.

But, in a live system where you delete rows, you will get rows inserted to fill in the gaps in blocks left by earlier deletions. This means that the highest rowid does not have to refer to the last inserted record.



Thanks for the update. I would be thankful to you if you demonstrate with an example.

Say we deleted a record.Can we restore the same rowid? Can the records inserted take the same rowid? Is it possible.


Thanks in advance,
Oli

[Updated on: Thu, 10 July 2008 04:48]

Report message to a moderator

Re: how to fetch last inserted record from oracle table? [message #332996 is a reply to message #332985] Thu, 10 July 2008 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to the previous answers, even if you don't delete rows and if you are using ASSM, Oracle put the rows randomly in a pool of blocks.
If you are not using ASSM and have several processes inserting at the same time, they each one insert into different blocks (if you insert sufficient rows).

Regards
Michel
Re: how to fetch last inserted record from oracle table? [message #333004 is a reply to message #332995] Thu, 10 July 2008 04:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's pretty easy to demonstrate. Run this, and you should see what happens:
create table test_0046 (col_1 number, col_2 number);

-- Insert more than a block worth of data.
insert into test_0046 select level, mod(level,100) from dual connect by level <= 2000;

-- Demonstrate that the data comes back in insert order
select col_1,col_2 from test_0046;

-- delete lots of data from the same block
delete test_0046 where col_1 <= 300;

-- Insert lots more data
insert into test_0046 select -1*level, 999 from dual connect by level <= 300;


select col_1,col_2 from test_0046;
icon10.gif  Re: how to fetch last inserted record from oracle table? [message #333011 is a reply to message #333004] Thu, 10 July 2008 05:08 Go to previous messageGo to next message
Chmartin26
Messages: 1
Registered: July 2008
Junior Member
I think without timestamp column you cannot accomplish correct results. Razz

--Chris

[Updated on: Thu, 10 July 2008 05:24] by Moderator

Report message to a moderator

Re: how to fetch last inserted record from oracle table? [message #333014 is a reply to message #333011] Thu, 10 July 2008 05:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Timestamp, or a column populated from a sequence.
Re: how to fetch last inserted record from oracle table? [message #333015 is a reply to message #333004] Thu, 10 July 2008 05:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the update.Now its pretty clear to me.
One question, can we the rowid be restored after deleling a row?


Regards,
Oli
Re: how to fetch last inserted record from oracle table? [message #333018 is a reply to message #333015] Thu, 10 July 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
One question, can we the rowid be restored after deleling a row?

What do you mean?
A rowid is (about) an address.
If you mean "reused", yes it may be reused.

Regards
Michel
Re: how to fetch last inserted record from oracle table? [message #333024 is a reply to message #333018] Thu, 10 July 2008 05:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Michel.[Smile] Now I would ask How?


SQL> SELECT COLUMN_1,ROWID FROM TEST_007;

  COLUMN_1 ROWID
---------- ------------------
         1 AAAKK5AAGAAA5wKAAA
         2 AAAKK5AAGAAA5wKAAB
         3 AAAKK5AAGAAA5wKAAC
         4 AAAKK5AAGAAA5wKAAD
         5 AAAKK5AAGAAA5wKAAE
         6 AAAKK5AAGAAA5wKAAF
         7 AAAKK5AAGAAA5wKAAG
         8 AAAKK5AAGAAA5wKAAH
         9 AAAKK5AAGAAA5wKAAI
        10 AAAKK5AAGAAA5wKAAJ

10 rows selected.

SQL> DELETE FROM TEST_007 WHERE ROWID='AAAKK5AAGAAA5wKAAD';




How it(rowid AAAKK5AAGAAA5wKAAD) can be reused?


Regards,
Oli
Re: how to fetch last inserted record from oracle table? [message #333027 is a reply to message #333024] Thu, 10 July 2008 05:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Re-run the example I posted, replacing this query:
select col_1,col_2 from test_0046;
with this
select col_1,col_2,rowid from test_0046;
and you'll see ROWIDs getting reused.
Re: how to fetch last inserted record from oracle table? [message #333029 is a reply to message #333024] Thu, 10 July 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If yuo leave your house someelse can move into it.
The same way if you delete a row, another can take the same place. It then has the same rowid.

Regards
Michel
Re: how to fetch last inserted record from oracle table? [message #333033 is a reply to message #333029] Thu, 10 July 2008 06:07 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks! Razz
Re: how to fetch last inserted record from oracle table? [message #333049 is a reply to message #333011] Thu, 10 July 2008 06:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Chmartin26 wrote on Thu, 10 July 2008 12:08
I think without timestamp column you cannot accomplish correct results. Razz

--Chris


Depends on the precision of the timestamp. You might end up with duplicates.
Re: how to fetch last inserted record from oracle table? [message #333073 is a reply to message #332659] Thu, 10 July 2008 07:46 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
anupam sao1 wrote on Wed, 09 July 2008 06:33
Thanks Dude


It took you 4 years to come up with that profound response?
Re: how to fetch last inserted record from oracle table? [message #333113 is a reply to message #333073] Thu, 10 July 2008 09:22 Go to previous message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Quote:
It took you 4 years to come up with that profound response?

I would guess that there is something wrong with the original 2 timestamps on the thread as the OP registered 4 years after the apparent original post.

Edit, Nope, just noticed his original original registered date, .... I'll just shutup now.

[Updated on: Thu, 10 July 2008 09:24]

Report message to a moderator

Previous Topic: generate numbers between 1 to 100
Next Topic: ORA-12838: cannot read/modify an object after modifying it in parallel
Goto Forum:
  


Current Time: Fri Dec 09 03:58:34 CST 2016

Total time taken to generate the page: 0.16199 seconds