订阅者消费日志-SYS_STREAMS

📄字数 2.3K

👁️阅读量加载中...

功能描述

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

字段说明

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

应用实例

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

sql

SQL> CREATE TABLE tab_modify(id INT);
-- 创建变更日志
SQL> EXEC DBMS_REPLICATION.CREATE_MODIFY_SOURCE('sysdba','tab_modify');
-- 创建变更日志访问订阅者
SQL> EXEC DBMS_REPLICATION.CREATE_SUBSCRIBER('sub_info','SYSDBA');

SQL> BEGIN
     FOR i IN 1..10 LOOP
     INSERT INTO tab_modify 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 |
+---------+--------+----------+---------+-------+---------+--------------------------+--------------------------+-----+------+---------+---------+----------+-------------------------------------------------+----------+
| 1167    | 8      | 0        | 1       | 1     | 1048584 | 2025-06-25 14:11:03.212  | 2025-06-25 14:11:03.212  | 0   | 0    | 0       | 68      | <BINARY> | SET SCHEMA TO SYSDBA;load meta                  | <NULL>   |
| 1277    | 10     | 0        | 1       | 1     | 1048584 | 2025-06-26 11:45:26.261  | 2025-06-26 11:45:26.261  | 0   | 128  | 0       | 57      | <BINARY> | SET SCHEMA TO SYSDBA;drop table tab_modify;     | <NULL>   |
| 1279    | 8      | 0        | 1       | 1     | 1048598 | 2025-06-26 11:45:33.290  | 2025-06-26 11:45:33.290  | 0   | 245  | 0       | 68      | <BINARY> | SET SCHEMA TO SYSDBA;load meta                  | <NULL>   |
| 1280    | 1      | 0        | 1       | 1     | 1048598 | 2025-06-26 11:45:40.361  | 2025-06-26 11:45:40.361  | 0   | 373  | 0       | 45      | <BINARY> | INSERT INTO SYSDBA.TAB_MODIFY ( ID ) VALUES (8) | <NULL>   |
| 1282    | 10     | 0        | 1       | 1     | 1048598 | 2025-06-26 11:46:16.724  | 2025-06-26 11:46:16.724  | 0   | 478  | 0       | 57      | <BINARY> | SET SCHEMA TO SYSDBA;drop table tab_modify;     | <NULL>   |
| 1147    | 8      | 0        | 3       | 1     | 1048599 | 2025-06-26 11:46:51.407  | 2025-06-26 11:46:51.407  | 0   | 595  | 0       | 68      | <BINARY> | SET SCHEMA TO SYSDBA;load meta                  | <NULL>   |
| 1150    | 1      | 0        | 3       | 1     | 1048599 | 2025-06-26 11:47:09.917  | 2025-06-26 11:47:09.917  | 0   | 723  | 0       | 45      | <BINARY> | INSERT INTO SYSDBA.TAB_MODIFY ( 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 |
+---------+--------+----------+---------+-------+---------+--------------------------+--------------------------+-----+------+---------+---------+----------+-------------------------------------------------+----------+
| 1167    | 8      | 0        | 1       | 1     | 1048584 | 2025-06-25 14:11:03.212  | 2025-06-25 14:11:03.212  | 0   | 0    | 0       | 68      | <BINARY> | SET SCHEMA TO SYSDBA;load meta                  | <NULL>   |
| 1277    | 10     | 0        | 1       | 1     | 1048584 | 2025-06-26 11:45:26.261  | 2025-06-26 11:45:26.261  | 0   | 128  | 0       | 57      | <BINARY> | SET SCHEMA TO SYSDBA;drop table tab_modify;     | <NULL>   |
| 1279    | 8      | 0        | 1       | 1     | 1048598 | 2025-06-26 11:45:33.290  | 2025-06-26 11:45:33.290  | 0   | 245  | 0       | 68      | <BINARY> | SET SCHEMA TO SYSDBA;load meta                  | <NULL>   |
| 1280    | 1      | 0        | 1       | 1     | 1048598 | 2025-06-26 11:45:40.361  | 2025-06-26 11:45:40.361  | 0   | 373  | 0       | 45      | <BINARY> | INSERT INTO SYSDBA.TAB_MODIFY ( ID ) VALUES (8) | <NULL>   |
| 1282    | 10     | 0        | 1       | 1     | 1048598 | 2025-06-26 11:46:16.724  | 2025-06-26 11:46:16.724  | 0   | 478  | 0       | 57      | <BINARY> | SET SCHEMA TO SYSDBA;drop table tab_modify;     | <NULL>   |
| 1147    | 8      | 0        | 3       | 1     | 1048599 | 2025-06-26 11:46:51.407  | 2025-06-26 11:46:51.407  | 0   | 595  | 0       | 68      | <BINARY> | SET SCHEMA TO SYSDBA;load meta                  | <NULL>   |
| 1150    | 1      | 0        | 3       | 1     | 1048599 | 2025-06-26 11:47:09.917  | 2025-06-26 11:47:09.917  | 0   | 723  | 0       | 45      | <BINARY> | INSERT INTO SYSDBA.TAB_MODIFY ( 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     | 1048600 | 0        | 0       | 828      | <NULL>    |
+-------+---------+----------+---------+----------+-----------+

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

订阅者消费日志-SYS_STREAMS ​

功能描述 ​

字段说明 ​

应用实例 ​

相关系统表 ​

订阅者消费日志-SYS_STREAMS

功能描述

字段说明

应用实例

相关系统表