Re: Display last records in a table

Yes to the scn (although I haven't a clue to how you determine this information) and to using logminer, but no to number 1. If you do not specify an order by in your select clause then you cannot guarrentee what order the data will come back in. The optimizer may in fact change how it gets the data. Also the "last rows" in a table may not be the last inserted rows in the table. By "last rows" I mean the ones with the highest rowid or largest block number in the table. Case in point. I insert a 1,000 rows in a table, commit, delete 10, commit, insert 20 rows. The last 20 rows could very well be scattered amongst where the 10 were that I deleted. So the "last rows" could be anything.

Also if it is important to know what the last rows were that were inserted then you need to make that an attribute of the row. e.g. a timestamp or sequence number.

Jim

"Jørn Hansen" <joh_at_stibo.dk> wrote in message news:990913184.469937_at_radon.stibo.dk...
> Dear Sybrand
>
>
> You say, it is not possible to find the last rows inserted into a table,
 as
> the rows are inserted at random. And of course, you are right. But there
 are
> ways to get a sense of it...
>
> - If the table has only been used for insertions, the rows should be
> inserted in an ordered fashion and it should be possible to find the last
> rows inserted simply by doing a 'SELECT * ...' and wait for the last rows
> fetched.
>
> - By looking for the SCN of the datablock, we should be able to find the
> block where the last commit was performed.
>
> - If the database is running in archive mode. One could use logminer to
 find
> the last insert-statements performed against the table.
>
> /Jørn
>
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> news:tgt1q3cclr1hd3_at_beta-news.demon.nl...
> >
> > "Thomas Stuefer" <stuefer_at_halli-data.at> wrote in message
> > news:5luP6.5$NU2.161908_at_news.salzburg-online.at...
> > > How can i display the last records in a table with an select-statement
 ?
> > >
> > > For example:
> > >
> > > I have an table with many thousand of records and only want to display
 the
> > > last 10 added records.
> > >
> > > Many thanks for your help !
> > >
> > > Tom
> > >
> > >
> > >
> >
> > If you don't have a timestamp in your record, this is not possible.
 Records
> > are inserted physically at random, there's no guarantee at all they will
 be
> > at the physical end of the table.
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
> >
> >
> >
>
>
Received on Sat May 26 2001 - 17:28:52 CDT