订阅者消费日志-sys_streams

功能描述

sys_streams系统表用于记载备份数据本次读取结束位置，系统包POLL_MODIFY_DATA参数KEEP_POS为true时生效。

字段说明

序号	字段名	类型	说明
0	DB_ID	OID_TYPE	库ID
1	SUBS_ID	OID_TYPE	订阅ID
2	PARTI_NO	INTEGER	分区号
3	FILE_NO	INTEGER	流服务的起始点对应的文件序号
4	FILE_OFF	BIGINT	流服务的起始点对应的分段内偏移量
5	RESERVED1	VARCHAR	保留字段

示例

查看订阅者各分区消费情况

sql

SQL> BEGIN
FOR i IN 1..10 LOOP
INSERT INTO modify_tb VALUES(i);
END LOOP;
END;
/

SQL> EXEC DBMS_REPLICATION.POLL_MODIFY_DATA('sub_info',0,null,1024,false,0,0);

TRAN_ID | ACTION | PARTI_NO | SRC_NID | DB_ID | OBJ_ID | DILIVER_TIME | COMMIT_TIME | FNO | FPOS | LOB_NUM | DAT_LEN | DATA | SQL | SQL_UNDO | 
------------------------------------------------------------------------------
7635 | 8 | 0 | 1 | 1 | 1048612 | 2022-05-11 14:29:12.751 AD | 2022-05-11 14:29:12.751 AD | 0 | 0 | 0 | 67 | <BINARY> | SET SCHEMA TO SYSDBA;load meta| <NULL>|
7651 | 1 | 0 | 1 | 1 | 1048612 | 2022-05-11 14:45:52.848 AD | 2022-05-11 14:45:52.869 AD | 0 | 127 | 0 | 44 | <BINARY> | INSERT INTO SYSDBA.MODIFY_TB ( ID ) VALUES (8)| <NULL>|   

SQL> SELECT * FROM sys_streams WHERE subs_id=(SELECT subs_id FROM sys_subscribers WHERE subs_name='SUB_INFO');

DB_ID | SUBS_ID | PARTI_NO | FILE_NO | FILE_OFF | RESERVED1 | 
------------------------------------------------------------------------------

SQL> EXEC DBMS_REPLICATION.POLL_MODIFY_DATA('sub_info',0,null,1024,true,0,0);

TRAN_ID | ACTION | PARTI_NO | SRC_NID | DB_ID | OBJ_ID | DILIVER_TIME | COMMIT_TIME | FNO | FPOS | LOB_NUM | DAT_LEN | DATA | SQL | SQL_UNDO | 
------------------------------------------------------------------------------
7635 | 8 | 0 | 1 | 1 | 1048612 | 2022-05-11 14:29:12.751 AD | 2022-05-11 14:29:12.751 AD | 0 | 0 | 0 | 67 | <BINARY> | SET SCHEMA TO SYSDBA;load meta| <NULL>|
7651 | 1 | 0 | 1 | 1 | 1048612 | 2022-05-11 14:45:52.848 AD | 2022-05-11 14:45:52.869 AD | 0 | 127 | 0 | 44 | <BINARY> | INSERT INTO SYSDBA.MODIFY_TB ( ID ) VALUES (8)| <NULL>|

SQL> SELECT * FROM sys_streams WHERE subs_id=(SELECT subs_id FROM sys_subscribers WHERE subs_name='SUB_INFO');

DB_ID | SUBS_ID | PARTI_NO | FILE_NO | FILE_OFF | RESERVED1 | 
------------------------------------------------------------------------------
1 | 1048613 | 0 | 0 | 231 | <NULL>|

根据FILE_NO和FILE_OFF与对应分区号的变更日志大小可分析当前同步情况

备注

变更记载数据查询只能在变更记载节点执行

订阅者消费日志-sys_streams ​

功能描述 ​

字段说明 ​

示例 ​

相关系统表 ​

备注 ​

订阅者消费日志-sys_streams

功能描述

字段说明

示例

相关系统表

备注