ShardingSphere-JDBC 5.5.2 encounters the error under Oracle: Table or view 'all_sequences' does not exist

[pan1998]

Which version of ShardingSphere did you use?

ShardingSphere 5.5.2

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

When I use ShardingSphere 5.5.2 in development environment with JDK11 + Oracle 11g + hibernate 5.6.15.Final + spring 5.2.20.RELEASE, I received the following error: "nested exception is org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'all_sequences' does not exist."

or "nested exception is org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException: Table or view 'all_synonyms' does not exist."

[terrymanu]

Problem Understanding

• Scenario: ShardingSphere-JDBC 5.5.2, Oracle 11g, Hibernate 5.6.15 + Spring 5.2.20.
• Symptom: Accessing Oracle dictionary views (e.g., ALL_SEQUENCES, ALL_SYNONYMS) throws Table or view '...' does not exist (TableNotFoundException).

Root Cause

• During binding, tables are allowed only if present in loaded metadata or in the system-table whitelist. The whitelist is built by SystemSchemaManager from schema///.yaml.
• The repository lacks Oracle system table/view definitions, and OracleSchemaOption#getDefaultSystemSchema() returns empty, so Oracle dictionary views are not recognized as system tables and are treated as missing by SimpleTableSegmentBinder#checkTableExists.

Analysis

• SystemSchemaManager expects resources under schema/{databaseType}/{schemaName}/{tableName}.yaml (see PostgreSQL at database/connector/dialect/postgresql/src/main/resources/schema/postgresql/pg_catalog/...).
• For Oracle, there is no schema/oracle/... directory and no default system schema, so ALL_SEQUENCES / ALL_SYNONYMS are not whitelisted, even though they should be treated as system views.
• This is a framework-side gap (missing system-table definitions and default system schema), not a user configuration issue.

Conclusion (with fix suggestion)

• We should fill Oracle system-table recognition:
  1. Add Oracle system table/view YAML definitions following schema/{db}/{schema}/{table}.yaml, e.g., database/connector/dialect/oracle/src/main/resources/schema/oracle/sys/all_sequences.yaml and .../all_synonyms.yaml (mirror the PostgreSQL YAML format with column definitions).
  2. Return a default system schema for Oracle, so dictionary views without explicit schema are recognized, e.g.:

         @Override
         public Optional<String> getDefaultSystemSchema() {
             return Optional.of("SYS");
         }

• Community PRs are warmly welcome to add these Oracle system-table YAMLs, default system schema, and tests; we will gladly review.

[manasagar]

@terrymanu To solve the current issue is it required to add all static dictionary views (about 2000) or some specific set.
I checked the static dictionary tables using
SELECT view_name
FROM all_views
WHERE owner = 'SYS'
AND (view_name LIKE 'ALL\%' ESCAPE '\'
OR view_name LIKE 'DBA\%' ESCAPE '\'
OR view_name LIKE 'USER\_%' ESCAPE '\')
AND view_name NOT LIKE '%\AW\%' ESCAPE '\'
ORDER BY view_name
reference https://docs.oracle.com/database/timesten-18.1/TTSYS/systemtables.htm