Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Oracle has SCN (System Change Numbers): <a href="http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm#CNCPT039" rel="noreferrer">http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm#CNCPT039</a></p> <blockquote> <p>A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.</p> <p>SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.</p> <p>Every transaction has an SCN. For example, if a transaction updates a row, then the database records the SCN at which this update occurred. Other modifications in this transaction have the same SCN. When a transaction commits, the database records an SCN for this commit.</p> </blockquote> <p><br> Use an ORA_ROWSCN pseudocolumn to examine current SCN of rows:<br> <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm#SQLRF51145" rel="noreferrer">http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm#SQLRF51145</a> <br><br> An example:</p> <pre><code>SELECT ora_rowscn, t.* From test t; </code></pre> <p>Demo --> <a href="http://www.sqlfiddle.com/#!4/535bc/1" rel="noreferrer">http://www.sqlfiddle.com/#!4/535bc/1</a> <br>(On SQLFiddle explicit commits apparently don't work - on a real database each commit increases SCN). <br><br><br> An example on a "real" database:</p> <pre><code>CREATE TABLE test( id int, value int ); INSERT INTO test VALUES(1,0); COMMIT; SELECT ora_rowscn, t.* FROM test t; ORA_ROWSCN ID VALUE ---------- ---------- ---------- 3160728 1 0 UPDATE test SET value = value + 1 WHERE id = 1; COMMIT; SELECT ora_rowscn, t.* FROM test t; ORA_ROWSCN ID VALUE ---------- ---------- ---------- 3161657 1 1 UPDATE test SET value = value + 1 WHERE id = 1; COMMIT; SELECT ora_rowscn, t.* FROM test t; ORA_ROWSCN ID VALUE ---------- ---------- ---------- 3161695 1 2 </code></pre> <p>If SCN of the transaction is know, we can use a flashback query to obtain a past value of the row:<br> <a href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131" rel="noreferrer">http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131</a> <br><br>An example:</p> <pre><code>SELECT t.*, versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation FROM test VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE t; ID VALUE VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ---------- ---------- ----------------- ------------------- --------------- ------------------- ---------------- ------------------ 1 2 3161695 13/12/10 08:19:39 06000300EA070000 U 1 1 3161657 13/12/10 08:18:39 3161695 13/12/10 08:19:39 06001200EA070000 U 1 0 3161657 13/12/10 08:18:39 SELECT t.*, versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation FROM test VERSIONS BETWEEN SCN 3161657 AND 3161657 t; ID VALUE VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ---------- ---------- ----------------- ------------------- --------------- ------------------- ---------------- ------------------ 1 1 3161657 13/12/10 08:18:39 06001200EA070000 U </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSQL Server : RowVersion equivalent in Oracle
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USkrokodilko
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for nice answer, but RowVersion is an actual column and is used for version stamping the column. The purpose of ORA_ROWSCN doesn't seems so. I have updated the question to give more perspective about RowVersion.
      singulars
      1. PO
      1. USFalaque
    2. CO@Falaque: No, Oracle doesn't have an **exact** equivalent of SQL Server ROWVERSION column. For optimistic concurrency ORA_ROWSCN is used. It would be helpful if you updated your question to give more perspective on why you want ROWVERSION in Oracle. If you are porting a database from SQL Server to Oracle then you need to omit ROWVERSION columns from tables and use ORA_ROWSCN in queries in place of whatever ROWVERSION columns were named. If that's a problem, consider it just a tip of the iceberg with things like very different transaction isolation models or string casing issues being next...
      singulars
      1. PO
      1. USTomek Szpakowicz
    3. CO@TomekSzpakowicz, thanks. I updated the question.
      singulars
      1. PO
      1. USFalaque
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload