{"record":{"id":"203d9ee18bfa06b7","repo":"apache/seatunnel","slug":"cannot-convert-timestamp-to-scn-make-sure-the-spe","errorCode":null,"errorMessage":"Cannot convert timestamp to SCN. Make sure the specified timestamp is valid.","messagePattern":"Cannot convert timestamp to SCN\\. Make sure the specified timestamp is valid\\.","errorType":"exception","errorClass":"SeaTunnelException","httpStatus":null,"severity":"error","filePath":"seatunnel-connectors-v2/connector-cdc/connector-cdc-oracle/src/main/java/org/apache/seatunnel/connectors/seatunnel/cdc/oracle/utils/OracleConnectionUtils.java","lineNumber":121,"sourceCode":"                    \"Converting timestamp {} to SCN with server time zone {}\",\n                    timestampMs,\n                    effectiveServerTimeZone);\n            String sql = \"SELECT TIMESTAMP_TO_SCN(?) AS SCN FROM DUAL\";\n            return jdbc.prepareQueryAndMap(\n                    sql,\n                    statement -> {\n                        java.sql.Timestamp timestamp = new java.sql.Timestamp(timestampMs);\n                        Calendar calendar =\n                                Calendar.getInstance(TimeZone.getTimeZone(effectiveServerTimeZone));\n                        statement.setTimestamp(1, timestamp, calendar);\n                    },\n                    rs -> {\n                        if (rs.next()) {\n                            final String scn = rs.getString(1);\n                            LOG.info(\"Converted timestamp {} to SCN: {}\", timestampMs, scn);\n                            return new RedoLogOffset(Scn.valueOf(scn).longValue());\n                        } else {\n                            throw new SeaTunnelException(\n                                    \"Cannot convert timestamp to SCN. Make sure the specified timestamp is valid.\");\n                        }\n                    });\n        } catch (SQLException e) {\n            LOG.error(\"Failed to convert timestamp to SCN\", e);\n            throw new SeaTunnelException(\"Failed to convert timestamp to SCN\", e);\n        }\n    }\n\n    public static List<TableId> listTables(\n            JdbcConnection jdbcConnection, String database, RelationalTableFilters tableFilters)\n            throws SQLException {\n        final List<TableId> capturedTableIds = new ArrayList<>();\n\n        Set<TableId> tableIdSet = new HashSet<>();\n        String queryTablesSql =\n                \"SELECT OWNER ,TABLE_NAME,TABLESPACE_NAME FROM ALL_TABLES \\n\"\n                        + \"WHERE PARTITIONED = 'YES' OR (TABLESPACE_NAME IS NOT NULL AND TABLESPACE_NAME NOT IN ('SYSAUX'))\";","sourceCodeStart":103,"sourceCodeEnd":139,"githubUrl":"https://github.com/apache/seatunnel/blob/cf67b549a7a6c35fa0beb12d83c62892427ea919/seatunnel-connectors-v2/connector-cdc/connector-cdc-oracle/src/main/java/org/apache/seatunnel/connectors/seatunnel/cdc/oracle/utils/OracleConnectionUtils.java#L103-L139","documentation":"OracleConnectionUtils.timestampToScn converts a timestamp to an SCN using Oracle's TIMESTAMP_TO_SCN function and throws SeaTunnelException if the query returns no row, meaning no SCN could be associated with the given timestamp. Oracle cannot map timestamps outside the range retained by its SCN-to-time mapping (bounded by undo/flashback and database uptime).","triggerScenarios":"Calling timestampToScn with a timestamp too old (beyond the SCN time mapping retention), a future timestamp, or a timestamp in the wrong time zone so the mapping query yields no result.","commonSituations":"startup.mode=timestamp with start time days/weeks in the past; recently restarted database losing older SCN-time mappings; time zone mismatch between configured serverTimeZone and actual DB time zone.","solutions":["Use a more recent timestamp within Oracle's SCN mapping retention (typically only hours to a few days)","Use startup.mode=latest or initial instead of timestamp for old starting points","Align serverTimeZone config with the Oracle server time zone","Check DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER availability and flashback configuration"],"exampleFix":"// before\nscan.startup.mode = timestamp\nscan.startup.timestamp = 1724000000000  // weeks old -> no SCN mapping\n// after\nscan.startup.mode = latest","handlingStrategy":"validation","validationCode":"long minBack = System.currentTimeMillis() - Duration.ofHours(1).toMillis();\nif (timestamp <= 0 || timestamp > System.currentTimeMillis() || timestamp < minBack) {\n    throw new IllegalArgumentException(\"timestamp outside SCN-mapping range; use a recent timestamp or startup.mode=latest\");\n}","typeGuard":null,"tryCatchPattern":"try {\n    RedoLogOffset off = OracleConnectionUtils.timestampToScn(conn, ts, tz);\n} catch (SeaTunnelException e) {\n    LOG.warn(\"No SCN for timestamp {}; fall back to latest\", ts);\n    // fallback: use currentRedoLogOffset(conn)\n}","preventionTips":["Only use timestamps within Oracle's SCN-time mapping retention","Prefer initial/latest startup modes for old starting points","Match serverTimeZone config to the DB server time zone","Avoid choosing timestamps from before a database restart"],"tags":["oracle-cdc","scn","timestamp","flashback"],"backgroundTag":"empty-result-set","analyzedSha":"cf67b549a7a6c35fa0beb12d83c62892427ea919","analyzedAt":"2026-09-10T21:44:55.265Z","contentChangedAt":"2026-09-10T21:44:55.265Z","schemaVersion":2},"datasetVersion":"2026-09-23T08:17:48.524Z"}