Skip to content

DBA_STREAMS

功能描述

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

字段说明

字段名类型说明
DB_IDINTEGER库ID
SUBS_IDINTEGER订阅ID
PARTI_NOINTEGER分区号
FILE_NOINTEGER流服务的起始点对应的文件序号
FILE_OFFBIGINT流服务的起始点对应的分段内偏移量
RESERVED1CHAR(-1)保留字段

示例

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

    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 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

备注

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