ORA_ROWSCN pseudo-column

OceanBase Database stores data in a multi-version manner. In a table, ORA_ROWSCN can be considered as a column, where the value represents the latest version number of each row. For two transactions t1 and t2, assuming t1 commits first, followed by t2, OceanBase Database ensures that the ORA_ROWSCN value after t1 is less than the ORA_ROWSCN value after t2. In OceanBase Database, ORA_ROWSCN is monotonically increasing. Unlike Oracle Database, where ORA_ROWSCN is a logical number within the system without practical significance, in OceanBase Database, ORA_ROWSCN represents the system time when the transaction that modified the data was committed. This value can be converted into meaningful time.

Considerations

• You can only use ORA_ROWSCN in a physical table, not in queries on views.
• ORA_ROWSCN can be used in expressions like WHERE.
• If a column is defined with the name ORA_ROWSCN, the pseudo-column ORA_ROWSCN will not be effective.
• You cannot use ORA_ROWSCN with the FLASHBACK QUERY statement, as it would create ambiguity in the semantics of ORA_ROWSCN.

Examples

1. Create a test table named tbl1.

   CREATE TABLE tbl1(col1 INT);
   

2. Insert test data into the tbl1 table.

   INSERT INTO tbl1 VALUES(10);
   

3. Insert three more test data records into the tbl1 table.

   INSERT INTO tbl1 VALUES(30),(40),(50);
   

4. Query the ORA_ROWSCN value corresponding to the col1 = 10 column in the tbl1 table.

   SELECT ORA_ROWSCN FROM tbl1 WHERE col1 = 10;
   

   The result is as follows:

   +---------------------+
   | ORA_ROWSCN          |
   +---------------------+
   | 1721975097797659000 |
   +---------------------+
   1 row in set
   

5. Query data with a specific ORA_ROWSCN value. For example, if multiple transactions commit data with the same ORA_ROWSCN value.

   1. Query the ORA_ROWSCN value corresponding to the col1 = 30/40/50 column in the tbl1 table.

      SELECT col1, ORA_ROWSCN FROM tbl1 WHERE col1 IN (30,40,50);
      

      The result is as follows:

      +------+---------------------+
      | COL1 | ORA_ROWSCN          |
      +------+---------------------+
      |   30 | 1721975326593884000 |
      |   40 | 1721975326593884000 |
      |   50 | 1721975326593884000 |
      +------+---------------------+
      3 rows in set
      

   2. Update the col1 value to 20 for rows in the tbl1 table where the ORA_ROWSCN value is specified.

      UPDATE tbl1 SET col1 = 20 WHERE ORA_ROWSCN = 1721975326593884000;
      

      The result is as follows:

      Query OK, 3 rows affected
      Rows matched: 3  Changed: 3  Warnings: 0
      

6. Use ORA_ROWSCN in an expression.

   SELECT col1, ORA_ROWSCN FROM tbl1 WHERE ORA_ROWSCN + 1 = 1721975097797659001;
   

   The result is as follows:

   +------+---------------------+
   | COL1 | ORA_ROWSCN          |
   +------+---------------------+
   |   10 | 1721975097797659000 |
   +------+---------------------+
   1 row in set