在用户排查数据问题时，往往希望知道某一行数据实际插入或更新的时间。OceanBase 数据库 MySQL 模式和 Oracle 模式中都提供了 ORA_ROWSCN 伪列来实现这一功能：ORA_ROWSCN 伪列将最新更改的系统更改号（SCN：System Change Number）反映到一行，该更改号表示这一行数据修改（INSERT、UPDATE）所在事务的提交时间。
详细说明
OceanBase 数据库 MySQL 模式租户
测试如下：
MySQL [test]> create table t1 (id int, name varchar(100));
Query OK, 0 rows affected (0.14 sec)
MySQL [test]> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
MySQL [test]> insert into t1 values (1,'jack');
Query OK, 1 row affected (0.01 sec)
MySQL [test]> insert into t1 values (2, 'tom');
Query OK, 1 row affected (0.01 sec)
MySQL [test]> insert into t1 values (3, 'jerry');
Query OK, 1 row affected (0.00 sec)
MySQL [test]> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | jack |
| 2 | tom |
| 3 | jerry |
+------+-------+
3 rows in set (0.00 sec)
MySQL [test]> select usec_to_time(ora_rowscn/1000) as time, * from t1;
+----------------------------+------+-------+
| time | id | name |
+----------------------------+------+-------+
| 2025-09-18 23:41:01.175308 | 1 | jack |
| 2025-09-18 23:41:12.906183 | 2 | tom |
| 2025-09-18 23:41:36.220282 | 3 | jerry |
+----------------------------+------+-------+
3 rows in set (0.01 sec)
MySQL [test]> update t1 set name='bob' where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [test]> select usec_to_time(ora_rowscn/1000) as time, * from t1;
+----------------------------+------+------+
| time | id | name |
+----------------------------+------+------+
| 2025-09-18 23:41:01.175308 | 1 | jack |
| 2025-09-18 23:41:12.906183 | 2 | tom |
| 2025-09-18 23:44:10.507149 | 3 | bob |
+----------------------------+------+------+
3 rows in set (0.00 sec)
OceanBase 数据库 Oracle 模式租户
测试如下：
obclient [SYS]> create table t2 (id int, name varchar2(100));
Query OK, 0 rows affected (0.195 sec)
obclient [SYS]> insert into t2 values (1,'jack');
Query OK, 1 row affected (0.027 sec)
obclient [SYS]> insert into t2 values (2, 'tom');
Query OK, 1 row affected (0.002 sec)
obclient [SYS]> commit;
Query OK, 0 rows affected (0.001 sec)
obclient [SYS]> insert into t2 values (3, 'jerry');
Query OK, 1 row affected (0.000 sec)
obclient [SYS]> commit;
Query OK, 0 rows affected (0.001 sec)
obclient [SYS]> select scn_to_timestamp(ora_rowscn) as time, t2.* from t2;
+---------------------------------+------+-------+
| TIME | ID | NAME |
+---------------------------------+------+-------+
| 18-SEP-25 03.51.17.417256000 PM | 1 | jack |
| 18-SEP-25 03.51.17.417256000 PM | 2 | tom |
| 18-SEP-25 03.53.32.483636000 PM | 3 | jerry |
+---------------------------------+------+-------+
3 rows in set (0.000 sec)
obclient [SYS]> select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS.FF9') as time, t2.* from t2;
+-------------------------------+------+-------+
| TIME | ID | NAME |
+-------------------------------+------+-------+
| 2025-09-18 15:51:17.417256000 | 1 | jack |
| 2025-09-18 15:51:17.417256000 | 2 | tom |
| 2025-09-18 15:53:32.483636000 | 3 | jerry |
+-------------------------------+------+-------+
3 rows in set (0.003 sec)
obclient [SYS]> update t2 set name='bob' where id=3;
Query OK, 1 row affected (0.007 sec)
Rows matched: 1 Changed: 1 Warnings: 0
obclient [SYS]> commit;
Query OK, 0 rows affected (0.001 sec)
obclient [SYS]> select to_char(scn_to_timestamp(ora_rowscn),'YYYY-MM-DD HH24:MI:SS.FF9') as time, t2.* from t2;
+-------------------------------+------+------+
| TIME | ID | NAME |
+-------------------------------+------+------+
| 2025-09-18 15:51:17.417256000 | 1 | jack |
| 2025-09-18 15:51:17.417256000 | 2 | tom |
| 2025-09-18 16:05:35.009747000 | 3 | bob |
+-------------------------------+------+------+
3 rows in set (0.000 sec)
注意事项
只能在实体表和物化视图中使用
ORA_ROWSCN，不能在对普通视图的查询中使用ORA_ROWSCN伪列。ORA_ROWSCN可以当成普通列一样出现在 WHERE 等表达式部分。因为
ORA_ROWSCN是非保留关键字，所以如果有列定义名字为ORA_ROWSCN，那么ORA_ROWSCN伪列将不会起作用。无法将
ORA_ROWSCN与FLASHBACK QUERY语句一起使用，否则ORA_ROWSCN的语义将产生歧义。
适用版本
OceanBase 数据库 V2.x、V3.x、V4.x 版本。
