DBA_STREAMS
功能描述
DBA_STREAMS系统视图用于记载备份数据本次读取结束位置,系统包POLL_MODIFY_DATA参数KEEP_POS为true时生效。
字段说明
字段名 | 类型 | 说明 |
---|---|---|
DB_ID | INTEGER | 库ID |
SUBS_ID | INTEGER | 订阅ID |
PARTI_NO | INTEGER | 分区号 |
FILE_NO | INTEGER | 流服务的起始点对应的文件序号 |
FILE_OFF | BIGINT | 流服务的起始点对应的分段内偏移量 |
RESERVED1 | CHAR(-1) | 保留字段 |
示例
查看订阅者各分区消费情况
sqlSQL> 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 dba_streams WHERE subs_id=(SELECT subs_id FROM dba_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 dba_streams WHERE subs_id=(SELECT subs_id FROM dba_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与对应分区号的变更日志大小可分析当前同步情况
相关系统视图
- dba_databases
- dba_subscribers
- dba_modify_logs
备注
变更记载数据查询只能在变更记载节点执行。