ORA_ROWSCN pseudocolumn

OceanBase Database stores multiple versions of data. In a table, ORA_ROWSCN can be considered as a column in the table that represents the latest version number of each data row. Assume that two transactions t1 and t2 are committed in order. The ORA_ROWSCN value obtained after t1 is committed is smaller than that obtained after t2 is committed. In OceanBase Database, the value of ORA_ROWSCN monotonically increments. In Oracle Database, ORA_ROWSCN is a logical number in the system and has no actual meaning. In OceanBase Database, ORA_ROWSCN is the system time when the transaction where the data modification occurs is committed. The value can be converted into a meaningful time.

Considerations

• ORA_ROWSCN can be used only in entity tables, but not in queries on views.
• ORA_ROWSCN can appear as a regular column in the expression part, such as in a WHERE clause.
• ORA_ROWSCN is not a reserved keyword. If a column is named ORA_ROWSCN in a table, the ORA_ROWSCN pseudocolumn does not take effect in the table.
• You cannot use ORA_ROWSCN in flashback queries. If you do, the semantics of ORA_ROWSCN will be ambiguous.

Examples

1. Create a table named tbl1.

   CREATE TABLE tbl1(col1 INT);
   

2. Insert a test data record 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. Obtain the ORA_ROWSCN value where col1 = 10 in the tbl1 table.

   SELECT ORA_ROWSCN FROM tbl1 WHERE col1 = 10;
   

   The return result is as follows:

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

5. Obtain the data with the specified ORA_ROWSCN value. Data committed in the same transaction has the same ORA_ROWSCN value.

   1. Obtain the ORA_ROWSCN values where col1 = 30/40/50 in the tbl1 table.

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

      The return result is as follows:

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

   2. Locate the row in the tbl1 table based on the specified ORA_ROWSCN value and change the col1 value of the row to 20.

      UPDATE tbl1 SET col1 = 20 WHERE ORA_ROWSCN = 1721975326593884000;
      

      The return 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 return result is as follows:

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