Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Display last records in a table

Re: Display last records in a table

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: Mon, 28 May 2001 21:31:39 +0800
Message-ID: <9etqvj$hue$1@news.seed.net.tw>

"Jørn Hansen" <joh_at_stibo.dk> ¼¶¼g©ó¶l¥ó 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.

No, even though there are only insertions occur on the table. There are many counterexamples:

  1. The DBA changes the PCTFREE storage parameter.
  2. The table is a clustered table.
  3. The table is an index-ogranized table.
  4. The table is a partitioned table.
  5. Some one uses the append, or parallel insert statement.
  6. The table has multiple free lists.

You may say that you don't use or know what are listed above, but, in a multi-user environment, the following case must occur:

7. Multiple users insert data into a block,

   another user inserts data into the next available block,    when they commit, the transaction entries for each transaction    in the previous block are freed,
   so the previous block may becomes available for insertion later. 8. Some one inserts data, the other one inserts data and commits,

   the pervious user rollbacks, then the space occpuied by the previous user    may become available for insertion later.

So, without a timestamp column in every record, it's impossible to guarantee the order of insertion.

> - 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.

The SCN is irrational here.
A SCN is correspondent to a commit, not to a row. Received on Mon May 28 2001 - 08:31:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US