如何通过Spark读取Oracle所有表名？遇ORA-00942报错求助

问题描述 #

我尝试通过Spark获取Oracle数据库中的表名，但执行时始终失败，报错信息如下：

Exception in thread "main" java.sql.SQLSyntaxErrorException:
ORA-00942: table or view does not exist
Sql = SELECT * FROM sys.tables WHERE 1=0, OriginalSql = SELECT * FROM
sys.tables WHERE 1=0, Error Msg = ORA-00942: table or view does not
exist

我的代码如下：

class TrxxConnection(sparkSession:SparkSession) {
  val trxxCon = sparkSession.read
    .format("jdbc")
    .option("url", "jdbc:oracle:thin:@//*****:****/****")
    .option("user", "*****")
    .option("password", "*****")
    .option("driver", "oracle.jdbc.driver.OracleDriver")

  // import sparkSession.implicits._
  // // List the name of all tables in ARAMIS Database
  def AllTableList =
      trxxCon
      .option("dbtable", "information_schema.tables") // or use sys.tables
      .load()
      .select("name").orderBy($"name".asc)
}

单元测试代码：

// below is unit test
println("Begin to establish connection to Oracle")
val AllTableList = new TrxxConnection(sparkSession).AllTableList // Dataset[Row] type
println("Connection to Oracle established")


println("Number of all  tables: " + AllTableList.count())
AllTableList.show()

请问我是否遗漏了什么？

问题原因及解决方法 #

• Oracle不支持sys.tables/information_schema.tables语法：你使用的是MySQL、SQL Server等数据库的系统表逻辑，但Oracle的系统视图命名规则完全不同。要获取表名，需使用以下Oracle专属系统视图：

  • user_tables：当前用户拥有的所有表
  • all_tables：当前用户有权限访问的所有表（含其他用户共享的表）
  • dba_tables：数据库中所有表（需DBA权限）

• 修正字段名与查询对象：Oracle中表名字段为table_name，而非你代码中的name。同时替换dbtable参数为正确的系统视图。

修改后的AllTableList方法示例：

def AllTableList =
    trxxCon
    .option("dbtable", "user_tables") // 按需替换为all_tables或dba_tables
    .load()
    .select("table_name").orderBy($"table_name".asc)

• 权限校验：若使用all_tables或dba_tables，需确保当前数据库用户拥有对应查询权限。可联系DBA授予SELECT ON all_tables权限或SELECT_CATALOG_ROLE角色。

内容的提问来源于stack exchange，提问作者yanachen