SQL Server : RowVersion equivalent in Oracle

Does Oracle has similar datatype to SQL Server’s RowVersion?

When you insert or update a row, the corresponding Version column(which is of type RowVersion) gets updated automatically.

  • SQL Server complaining about triggers returning result sets?
  • On Update: Auto Update Date/Time Field
  • error in Multiple Select statements in Insert statement
  • Order parent child records by parent group and children
  • Are Microsoft SQL Server “Express” editions free to develop on?
  • Distributed transactions between MySQL and MSSQL
  • MSDN says about RowVersion:

    • Is a data type that exposes automatically generated, unique binary
      numbers within a database. rowversion is generally used as a mechanism
      for version-stamping table rows. The storage size is 8 bytes. The
      rowversion data type is just an incrementing number and does not
      preserve a date or a time.

    • Each database has a counter that is incremented for each insert or
      update operation that is performed on a table that contains a
      rowversion column within the database. This counter is the database
      rowversion. This tracks a relative time within a database, not an
      actual time that can be associated with a clock. A table can have only
      one rowversion column. Every time that a row with a rowversion column
      is modified or inserted, the incremented database rowversion value is
      inserted in the rowversion column.

    • You can use the rowversion column of a row to easily determine whether
      any value in the row has changed since the last time it was read. If
      any change is made to the row, the rowversion value is updated. If no
      change is made to the row, the rowversion value is the same as when it
      was previously read.

    • You can add a rowversion column to a table to help maintain the
      integrity of the database when multiple users are updating rows at the
      same time. You may also want to know how many rows and which rows were
      updated without re-querying the table.

    We are designing a data model with oracle and would like to use Version column to manage concurrency.

    I would also like to know if there is better way in Oracle world.

  • Convert MS SQL script to Mysql and Oracle
  • why ss round to nearest minute that will change day
  • How to insert into SQL Server table from Oracle table?
  • DbCommand and parameterized SQL, ORACLE vs SQL Server
  • When are nulls “safe” in a column?
  • How to find the ODBC driver name for a connection string?
  • 3 Solutions collect form web for “SQL Server : RowVersion equivalent in Oracle”

    The simple answer is No – but it’s easy to create one yourself with a NUMBER column and a trigger to set/update it.

    A simple example for Oracle 11gR2:

    CREATE SEQUENCE global_rowversion_seq;
    
    ALTER TABLE mytable1 ADD rowversion NUMBER;
    
    ALTER TABLE mytable2 ADD rowversion NUMBER;
    
    CREATE TRIGGER mytable1_biu
       BEFORE INSERT OR UPDATE
       ON mytable1
       FOR EACH ROW
    BEGIN
      :NEW.rowversion := global_rowversion_seq.NEXTVAL;
    END mytable1_biu;
    
    CREATE TRIGGER mytable2_biu
      BEFORE INSERT OR UPDATE
      ON mytable2
      FOR EACH ROW
    BEGIN
      :NEW.rowversion := global_rowversion_seq.NEXTVAL;
    END mytable2_biu;
    

    (If you’re on an earlier Oracle version, the assignments in the triggers must be done with a query, e.g.:

      SELECT global_rowversion_seq.NEXTVAL
      INTO :NEW.rowversion
      FROM dual;
    

    Now, keep in mind in some cases this design may have a performance impact in extreme situations (e.g. databases with extremely high insert/update activity) due to contention from all database inserts/updates using the same sequence. Of course, in this circumstance you probably would avoid triggers in the first place anyway.

    Depending on how you use the rowversion column, it may be a good idea to use a separate sequence for each table instead. This would mean, of course, that rowversion would no longer be globally unique – but if you are only interested in comparing changes to rows within a table, then this would be fine.

    Another approach is to advance the counter for each row individually – this doesn’t need a sequence and allows you to detect changes to a row (but does not allow comparing any row to another row):

    ALTER TABLE mytable ADD rowversion NUMBER;
    
    CREATE TRIGGER mytable_biu
      BEFORE INSERT OR UPDATE
      ON mytable
      FOR EACH ROW
    BEGIN
      :NEW.rowversion := NVL(:OLD.rowversion, 0) + 1;
    END mytable_biu;
    

    Each row will be inserted with rowversion = 1, then subsequent updates to that row will increment it to 2, 3, etc.

    Oracle has SCN (System Change Numbers): http://docs.oracle.com/cd/E11882_01/server.112/e10713/transact.htm#CNCPT039

    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.

    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.

    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.

    Use an ORA_ROWSCN pseudocolumn to examine current SCN of rows:
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns007.htm#SQLRF51145

    An example:

    SELECT ora_rowscn, t.* From test t;
    

    Demo –> http://www.sqlfiddle.com/#!4/535bc/1
    (On SQLFiddle explicit commits apparently don’t work – on a real database each commit increases SCN).

    An example on a “real” database:

    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 
    

    If SCN of the transaction is know, we can use a flashback query to obtain a past value of the row:
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131

    An example:

    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                               
    

    As per oracle Documentation you can use ORA_ROWSCN, and using “ROWDEPENDENCIES” to make oracle row-level dependency tracking. not per physical data block.

    Ref: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm

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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.