---
title: 通过伪列 ORA_ROWSCN 来查看数据插入或更新的时间戳-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于通过伪列 ORA_ROWSCN 来查看数据插入或更新的时间戳相关的常见问题和使用技巧，帮助您快速解决通过伪列 ORA_ROWSCN 来查看数据插入或更新的时间戳的难题。
---
切换语言

- 简体中文
- English

划线反馈

# 通过伪列 ORA_ROWSCN 来查看数据插入或更新的时间戳

更新时间：2026-05-28 02:06

适用版本： V2.1.x、V2.2.x、V3.1.x、V3.2.x、V4.0.x、V4.1.x、V4.2.x、V4.3.x、V4.4.x 内容类型：TechNote  

在用户排查数据问题时，往往希望知道某一行数据实际插入或更新的时间。OceanBase 数据库 MySQL 模式和 Oracle 模式中都提供了 `ORA_ROWSCN` 伪列来实现这一功能：`ORA_ROWSCN` 伪列将最新更改的系统更改号（SCN：System Change Number）反映到一行，该更改号表示这一行数据修改（INSERT、UPDATE）所在事务的提交时间。

## 详细说明

### OceanBase 数据库 MySQL 模式租户

**测试如下：**

```shell
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 模式租户

**测试如下：**

```shell
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]> 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]> 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 版本。

Previous

[Oceanbase 数据库 Oracle 模式下批量执行 INSERT 语句时，参数中包含 NULL，无法触发批量优化](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000006346774)

Next

[打开 _enable_enhanced_cursor_validation 后 DML 修改的表个数超过 8 个则 cursor 可能读不到事务内修改](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000003228213) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线