oracle 记录每一条数据最后更新的时间(ora_rowscn)

本文介绍了Oracle数据库中ORA_ROWSCN的功能及其使用方法。ORA_ROWSCN是一个伪列,用于记录行数据的最后一次更改时间。文章还提供了查询ORA_ROWSCN的具体SQL语句示例,并解释了其在数据校验和增量操作中的应用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

oracle ora_rowscn
        ora_rowscn是oracle的伪列,10g版本后才有,记录时间插入的时间和最后更新的时间
       
        SELECT TO_CHAR(scn_to_timestamp(ORA_ROWSCN),'YYYY-MM-DD HH24:MI:SS') INSERT_TIME FROM TABLE_NAME WHERE ZDNAME = {1}
        查询数据最后更新的时间
        ora_rowscn 只能查询近期的插入的时间(5天)
        select to_char(scn_to_timestamp(min(scn)),'yyyy-mm-dd hh24:mi:ss') from sys.smon_scn_time ;
       可以用来校验数据,和增量操作数据等
            
{"data":[{"UPDATE_TIME":"2025-06-04 17:17:03","UPDATE_USER":"NULL","INDICATOR_ID":"21102","GRANULARITY":"year","START_DATE":"2024-12-31 08:00:00","END_DATE":"2025-12-31 08:00:00","LOCK_FLAG":"0","VALUE_TYPE":"0","ALLOW_UNLOCK_FLAG":"1","TENANT_ID":"1","VER_UUID":"-1","MODIFY_FLAG":"0"}],"database":"ENERGY_TEST","es":1749028623,"id":0,"isDdl":false,"mysqlType":null,"old":[{"INDICATOR_VAL_ID":"15828195","CREATE_TIME":"2024-12-31 08:14:24","CREATE_USER":"NULL","UPDATE_TIME":"2025-06-04 17:04:49","UPDATE_USER":"NULL","DEL":"0","REMARK":"NULL","INDICATOR_ID":"21102","GRANULARITY":"year","SHIFT_NAME":"NULL","TEAM_NAME":"NULL","BELONG_DATE":"2025-01-01 00:00:00","START_DATE":"2024-12-31 08:00:00","END_DATE":"2025-12-31 08:00:00","LOCK_FLAG":"0","VALUE_TYPE":"0","ALLOW_UNLOCK_FLAG":"1","TENANT_ID":"1","VER_UUID":"-1","MODIFY_FLAG":"0"}],"pkNames":["INDICATOR_VAL_ID","INDICATOR_VAL_ID"],"sql":"update \"ENERGY_TEST\".\"CFG_INDICATOR_VAL\" set \"UPDATE_TIME\" = TIMESTAMP ' 2025-06-04 17:17:03', \"UPDATE_USER\" = NULL, \"INDICATOR_ID\" = 21102, \"GRANULARITY\" = 'year', \"START_DATE\" = TIMESTAMP ' 2024-12-31 08:00:00', \"END_DATE\" = TIMESTAMP ' 2025-12-31 08:00:00', \"LOCK_FLAG\" = 0, \"VALUE_TYPE\" = 0, \"ALLOW_UNLOCK_FLAG\" = 1, \"TENANT_ID\" = 1, \"VER_UUID\" = '-1', \"MODIFY_FLAG\" = 0 where \"INDICATOR_VAL_ID\" = 15828195 and \"CREATE_TIME\" = TIMESTAMP ' 2024-12-31 08:14:24' and \"CREATE_USER\" IS NULL and \"UPDATE_TIME\" = TIMESTAMP ' 2025-06-04 17:04:49' and \"UPDATE_USER\" IS NULL and \"DEL\" = 0 and \"REMARK\" IS NULL and \"INDICATOR_ID\" = 21102 and \"GRANULARITY\" = 'year' and \"SHIFT_NAME\" IS NULL and \"TEAM_NAME\" IS NULL and \"BELONG_DATE\" = TIMESTAMP ' 2025-01-01 00:00:00' and \"START_DATE\" = TIMESTAMP ' 2024-12-31 08:00:00' and \"END_DATE\" = TIMESTAMP ' 2025-12-31 08:00:00' and \"LOCK_FLAG\" = 0 and \"VALUE_TYPE\" = 0 and \"ALLOW_UNLOCK_FLAG\" = 1 and \"TENANT_ID\" = 1 and \"VER_UUID\" = '-1' and \"MODIFY_FLAG\" = 0","sqlType":null,"table":"CFG_INDICATOR_VAL","ts":1749028624420,"type":"UPDATE"}这是kafka接收到的日志
最新发布
06-05
<think>嗯,用户正在深入研究Oracle数据库变更捕获的细节问题。之前的对话历史显示用户已经了解触发器和Canal解析的关系,现在想通过Kafka日志分析触发器的具体行为。用户显然是个有经验的数据库开发者或架构师,需要解决实际生产中数据同步的疑难杂症。用户提到三个关键引用:第一个是SQLServer变更捕获方案(虽然和Oracle无关,但原理相通),第二个是Java实现数据同步的架构(用户可能实际在用类似方案),第三个是数据存储体系差异(说明用户关注数据流向)。但用户真实需求很明确:解析Kafka中的Oracle日志,特别关注触发器如何影响数据变更记录。用户的问题其实包含三个技术层面:一是日志解码,二是触发器行为追踪,三是变更内容还原。需要提供可操作的方案,而不是理论说明。上次回复提到的LogMiner方法值得展开,因为这是最接近数据库底层的验证手段。特别注意用户环境是Oracle→Canal→Kafka的数据管道。当触发器执行DML时,会在同一事务中产生多个操作记录,而Canal默认按提交顺序推送到Kafka。这解释了为什么用户可能看到INSERT变成UPDATE——触发器修改了同一条新插入的记录。关键突破点在于两个技术细节:Oracle的SCN(SystemChangeNumber)机制保证事务内操作有序,以及LogMiner解析redolog时如何标记操作类型(OPERATION列)。建议用户对比同一SCN下的多条SQL_REDO语句,这样能清晰看到触发器添加的操作。用户可能没明说但隐含的需求是:如何区分原始操作和触发器操作?这个需要组合使用ORA_ROWSCN伪列和DML触发器的条件判断。最好给出具体示例,比如在触发器中添加操作类型标记字段。关于Kafka日志解析工具的选择,Debezium确实比Canal更擅长处理CDC元数据,但迁移成本较高。如果用户环境允许,可以演示如何用kafkacat快速过滤异常事务。</think> 要解析Kafka中的Oracle数据库变更日志(特别是含触发器的场景),需通过以下步骤实现。结合Canal/Ogg或Debezium等CDC工具的数据流,以下是完整的分析流程与技术方案: --- ### **一、Kafka日志解析的核心流程** #### 1. **识别CDC工具元数据结构** 假设使用**Debezium for Oracle**,其Kafka消息格式包含: ```json { "before": {...}, // 变更前行数据 "after": {...}, // 变更后行数据 "op": "u", // 操作类型:c=插入, u=更新, d=删除 "ts_ms": 1620000000,// 变更时间戳 "source": { "table": "CFG_INDICATOR_VAL", "scn": "123456", // Oracle系统变更号(SCN) "transaction_id": "tx0001" // 事务ID } } ``` #### 2. **触发器的日志特征分析** 当表存在触发器时,**单次操作会产生多个日志事件**,例如: - `INSERT`操作(原始) → 触发器更新当前行 → **Kafka中连续出现两条消息** ```json // 原始INSERT {"op":"c", "after":{"id":101, "value":100, "version":1} } // 触发器引发的UPDATE {"op":"u", "before":{"id":101, "value":100, "version":1}, "after": {"id":101, "value":150, "version":2} } ``` --- ### **二、解析触发器操作的验证方法** #### 1. **通过SCN关联事务** 利用`source.scn`或`source.transaction_id`字段合并同一事务的操作: ```python # Python示例:使用Kafka消费者API from kafka import KafkaConsumer consumer = KafkaConsumer('oracle_cdc_topic') for msg in consumer: payload = json.loads(msg.value) scn = payload['source']['scn'] # 聚合相同SCN的所有变更 if scn not in transaction_map: transaction_map[scn] = [] transaction_map[scn].append(payload) ``` #### 2. **定位触发器变更的特征** - **特征1**:同一条记录(相同主键)在**同一事务中出现多次变更** - **特征2**:`before`数据与前一操作的`after`数据完全一致 - **特征3**:首次操作类型为`INSERT`,后续出现无业务逻辑的`UPDATE` --- ### **三、触发器行为的还原与影响分析** #### 1. **数据变更追溯** | 操作顺序 | 操作类型 | 数据版本 | 来源 | |----------|----------|----------|--------------| | 1 | INSERT | v1 | 应用层SQL | | 2 | UPDATE | v2 | 触发器逻辑 | | 3 | UPDATE | v3 | 业务逻辑更新 | #### 2. **触发器引发的风险** - **数据漂移**:Kafka消费者可能错误处理中间状态(如消费v1版本后触发业务逻辑)[^2] - **顺序错乱**:网络延迟导致`UPDATE`事件早于`INSERT`到达,破坏数据一致性 ```mermaid graph LR A[应用发起INSERT] --> B(触发器自动UPDATE) B --> C[写入Redo日志] C --> D[CDC解析为两个事件] D --> E[Kafka顺序:INSERT→UPDATE] E --> F{风险:若INSERT未到达,UPDATE视为异常} ``` --- ### **四、企业级解决方案** #### 1. **禁用触发器副作用**(推荐) ```sql -- 修改触发器:避免更新主表 CREATE OR REPLACE TRIGGER CFG_INDICATOR_VAL_TRIGGER BEFORE INSERT ON CFG_INDICATOR_VAL FOR EACH ROW BEGIN -- 改为写入辅助表 INSERT INTO AUDIT_LOG_TABLE VALUES (:new.id, SYSDATE); END; ``` > ✅ **效果**:Kafka日志仅保留原始`INSERT`事件 #### 2. **使用Debezium事务元数据** 启用`transaction.metadata`配置,明确事务边界: ```yaml debezium.source.transaction.metadata.enabled=true ``` Kafka中生成`_transaction_metadata`消息,包含: ```json { "id": "tx0001", "event_count": 2, // 本事务含2个事件 "data_collections": ["SCHEMA.CFG_INDICATOR_VAL"] } ``` #### 3. **消费端合并逻辑** ```python def merge_transaction(events): final_data = events[0]['after'] # 取初始INSERT数据 for event in events[1:]: if event['op'] == 'u': final_data.update(event['after']) # 合并后续变更 return final_data # 得到触发器修正后的终态数据 ``` --- ### **五、验证工具推荐** 1. **LogMiner**:直接解析Oracle Redo日志 ```sql SELECT sql_redo, scn FROM v$logmnr_contents WHERE table_name='CFG_INDICATOR_VAL' AND scn='123456'; ``` 2. **Kafkacat**:实时查看Kafka原始消息 ```bash kafkacat -b kafka:9092 -t oracle_cdc_topic -C ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值