Overview
A configured Oracle data source may fail to start with an error stating the connection is not valid, followed by a report of the v$session.osuser
property and value.
Applies To
Any Dremio connection to an Oracle source, where the Dremio software is being run by a user with a name in the operating system longer than 30 characters.
Details
In the Dremio console, the source will fail to save with an error banner:
And the server.log will show a message similar to the following, from one of several possible thread sources:
2022-04-20 17:44:44,022 [start-Oracle-source] ERROR c.d.e.s.jdbc.JdbcSchemaFetcherImpl - Connection is not valid.
java.sql.SQLException: Connection property: format error: Property is 'v$session.osuser' and value is 'veryverylongusernamereally@ad.dremio.com'
at oracle.jdbc.driver.T4CConnection.validateConnectionProperties(T4CConnection.java:6671)
at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:1214)
...
Cause
This is caused when the Dremio process is run by a user who has a name in the host operating system of more than 30 characters. This can apply to a named user, machine account, service daemon, etc… The name of the user running the process is passed to the Java Virtual Machine as the user.name
property, which is passed to the Oracle JDBC thin client used in the Oracle JDBC listener and client code. Due to a validation test of (this.thinVsessionOsuser.length() > 30)
the property fails to validate with a long string, resulting in a failure to build the v$session
table for the Oracle DB.
Workaround
Users may configure the v$session.osuser
property manually in the Dremio console by navigating to the “Settings > Advanced Options > Connection Properties” page an Oracle data source. Enter the name of the property as "v$session.osuser" and the value to any string 29 characters or less.
Solution
There is no known solution at this time. This is caused by software not owned or maintained by Dremio.
Further Reading
Oracle javadocs: https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_THIN_VSESSION_OSUSER
Oracle V$SESSION documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-SESSION.html#GUID-28E2DC75-E157-4C0A-94AB-117C205789B9