[Bug] Oracle source runs, and the number of oracle connections keeps increasing

[spoon-lz]

Search before asking

• I searched in the issues and found nothing similar.

Flink version

1.13

Flink CDC version

2.3

Database and its version

oracle 19c

Minimal reproduce step

I used oracle cdc incrementally to consume oracle data, and after running it for a while, I found that the number of oracle connections was high

code:

 OracleSource.Builder builder = com.ververica.cdc.connectors.oracle.OracleSource.<String>builder()
                .database(source.getDatabase())
                .schemaList(source.getSchemaList())
                .tableList(source.getTable())
                .username(source.getUsername())
                .password(source.getPassword())
                .startupOptions(StartupOptions.latest())
                .deserializer(new JsonDebeziumDeserializationSchema())
                .debeziumProperties(properties);

What did you expect to see?

The number of oracle connections held by the flink taskmanager is within a reasonable range

What did you see instead?

The DBA reported that too many connections were putting pressure on oracle

Anything else?

No response

Are you willing to submit a PR?

• I'm willing to submit a PR!

[spoon-lz]

The executingRPCSQL inside these oracle.jdbc.driver.T4CConnection displays all: ["BEGIN sys.dbms_logmnr.start_logmnr(startScn => '17763342105408', endScn => '17763342205408', OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.NO_ROWID_IN_STMT);END;"]

[spoon-lz]

Several other executingRPCSQL are found among these T4CConnections like: SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID FROM V$LOGMNR_CONTENTS WHERE SCN > :1 AND SCN <= :2 AND SRC_CON_NAME = 'RESDB' AND ((OPERATION_CODE IN (6,7,34,36) OR (OPERATION_CODE = 5 AND USERNAME NOT IN ('SYS','SYSTEM','**') AND INFO NOT LIKE 'INTERNAL DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%')) ) OR (OPERATION_CODE IN (1,2,3) AND TABLE_NAME != 'LOG_MINING_FLUSH' AND SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB') AND (REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^**$','i')) ))

[ruanhang1993]

@spoon-lz Thanks a lot for the analysis. I will raise a PR to fix it.

[jiaojietao]

Several other executingRPCSQL are found among these T4CConnections like: SELECT SCN, SQL_REDO, OPERATION_CODE, TIMESTAMP, XID, CSF, TABLE_NAME, SEG_OWNER, OPERATION, USERNAME, ROW_ID, ROLLBACK, RS_ID FROM V$LOGMNR_CONTENTS WHERE SCN > :1 AND SCN <= :2 AND SRC_CON_NAME = 'RESDB' AND ((OPERATION_CODE IN (6,7,34,36) OR (OPERATION_CODE = 5 AND USERNAME NOT IN ('SYS','SYSTEM','**') AND INFO NOT LIKE 'INTERNAL DDL%' AND (TABLE_NAME IS NULL OR TABLE_NAME NOT LIKE 'ORA_TEMP_%')) ) OR (OPERATION_CODE IN (1,2,3) AND TABLE_NAME != 'LOG_MINING_FLUSH' AND SEG_OWNER NOT IN ('APPQOSSYS','AUDSYS','CTXSYS','DVSYS','DBSFWUSER','DBSNMP','GSMADMIN_INTERNAL','LBACSYS','MDSYS','OJVMSYS','OLAPSYS','ORDDATA','ORDSYS','OUTLN','SYS','SYSTEM','WMSYS','XDB') AND (REGEXP_LIKE(SEG_OWNER || '.' || TABLE_NAME,'^**$','i')) ))
May I ask how you achieved obtaining the ROWID field from Oracle