Home » SQL & PL/SQL » SQL & PL/SQL » Last Record (oracle 11g, 11.2.0.4 , windows)
Last Record [message #651916] Fri, 27 May 2016 07:27 Go to next message
Satheesh.A
Messages: 9
Registered: May 2016
Location: Chennai
Junior Member
How to find last inserted records from table using oracle 11g?
Re: Last Record [message #651917 is a reply to message #651916] Fri, 27 May 2016 07:29 Go to previous messageGo to next message
John Watson
Messages: 8580
Registered: January 2010
Location: Global Village
Senior Member
That would depend on how (or if) you record the time of insertion. If you do not record this, there is no precise method.

By the way, you will not get a job with Oracle if you say "record" when you mean "row".
Re: Last Record [message #651918 is a reply to message #651916] Fri, 27 May 2016 07:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Satheesh.A wrote on Fri, 27 May 2016 05:27
How to find last inserted records from table using oracle 11g?

assuming you have all archived REDO log files, use DBMS_LOGMNR.
Oracle does not explicitly track this detail.
Re: Last Record [message #651923 is a reply to message #651918] Fri, 27 May 2016 12:36 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
You should be using a sequence or a timestamp in every row to determine the row creation time or order.
Re: Last Record [message #651929 is a reply to message #651916] Fri, 27 May 2016 14:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Other method: create your table with ROWDEPENDENCIES property.

Re: Last Record [message #651930 is a reply to message #651929] Fri, 27 May 2016 15:01 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Very cool feature Michel. Nice to know.
Re: Last Record [message #651943 is a reply to message #651930] Fri, 27 May 2016 23:32 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Hi

Are you looking specifically for 11g or even 10g is fine?

You can do the following ways:

1. SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP)

2. SELECT * FROM(SELECT * FROM EMP ORDER BY ROWID DESC) WHERE ROWNUM < 2

3. Use analytical function like rank applied on rowid

Garan
Re: Last Record [message #651945 is a reply to message #651943] Sat, 28 May 2016 00:01 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All are wrong.
ROWID are NOTHING to do with time.

Re: Last Record [message #651946 is a reply to message #651945] Sat, 28 May 2016 00:13 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
CREATE TABLE EMP(ID NUMBER)

INSERT INTO EMP VALUES(20);
INSERT INTO EMP VALUES(10);
INSERT INTO EMP VALUES(15);

Here the last row inserted is value 15 and the output from my below query gives 15.

SELECT * FROM EMP WHERE ROWID=(SELECT MAX(ROWID) FROM EMP)

let me ask OP this is what he is expecting if I understand his question
Re: Last Record [message #651949 is a reply to message #651946] Sat, 28 May 2016 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is wrong, once again ROWID has NOTHING to do with time, "max(rowid)" and "last inserted" matches only by luck.

Re: Last Record [message #651950 is a reply to message #651949] Sat, 28 May 2016 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See this example:
SQL> create table t (id int, v char(100) default 'X');

Table created.

SQL> insert into t (id) select level from dual connect by level <= 100;

100 rows created.

SQL> commit;

Commit complete.

SQL> delete t where id between 10 and 20;

11 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into t(id) values (0);

1 row created.

SQL> select id from t where rowid = (select max(rowid) from t);
        ID
----------
       100

1 row selected.

100 where last inserted is 0.
Re: Last Record [message #651951 is a reply to message #651950] Sat, 28 May 2016 01:16 Go to previous messageGo to next message
garan
Messages: 27
Registered: January 2016
Junior Member
Michael

I agree with you rowid cannot be used.

I saw similar post below in the below URL that rowid is just a physical pointer and no way it indicates order of insertion

http://www.dba-oracle.com/t_oracle_find_last_row.htm

garan

Re: Last Record [message #651952 is a reply to message #651951] Sat, 28 May 2016 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67923
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This site is b.....t, forget it.
Sometimes it is correct, mostly when it copies information from other sites) but often it is wrong.

Re: Last Record [message #651959 is a reply to message #651929] Sat, 28 May 2016 06:37 Go to previous message
Solomon Yakobson
Messages: 3067
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Fri, 27 May 2016 15:59

Other method: create your table with ROWDEPENDENCIES property.



No, it will not allow to find last row. ALL rows affected by ALL statements withing transaction will have same ORA_ROWSCN.

SQL> select l,ora_rowscn from tbl;

         L ORA_ROWSCN
---------- ----------
         1   42951355
         2   42951355
         3   42951355
         4   42951355
         5   42951355
         6   42951355
         7   42951355
         8   42951355
         9   42951355
        10   42951355

10 rows selected.

SQL> update tbl set l = l where l = 5;

1 row updated.

SQL> select l,ora_rowscn from tbl;

         L ORA_ROWSCN
---------- ----------
         1   42951355
         2   42951355
         3   42951355
         4   42951355
         5
         6   42951355
         7   42951355
         8   42951355
         9   42951355
        10   42951355

10 rows selected.

SQL> update tbl set l = l where l = 3;

1 row updated.

SQL> select l,ora_rowscn from tbl;

         L ORA_ROWSCN
---------- ----------
         1   42951355
         2   42951355
         3
         4   42951355
         5
         6   42951355
         7   42951355
         8   42951355
         9   42951355
        10   42951355

10 rows selected.

SQL> insert into tbl values(11);

1 row created.

SQL> select l,ora_rowscn from tbl;

         L ORA_ROWSCN
---------- ----------
         1   42951355
         2   42951355
         3
         4   42951355
         5
         6   42951355
         7   42951355
         8   42951355
         9   42951355
        10   42951355
        11

11 rows selected.

SQL> commit;

Commit complete.

SQL> select l,ora_rowscn from tbl;

         L ORA_ROWSCN
---------- ----------
         1   42951355
         2   42951355
         3   42951706 <---
         4   42951355
         5   42951706 <---
         6   42951355
         7   42951355
         8   42951355
         9   42951355
        10   42951355
        11   42951706 <---

11 rows selected.

SQL>


As you can see there is no way to tell what row was last updated/inserted.

SY.

[Updated on: Sat, 28 May 2016 07:00]

Report message to a moderator

Previous Topic: Indexes
Next Topic: Empolyees doesn't have any reportess
Goto Forum:
  


Current Time: Sun Aug 01 18:12:17 CDT 2021