How to read all table names from Oracle thru Spark-CodePudding

I want to fetch table names from Oracle database via Spark.

But it always failed. with error:

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 orview does not exist

Have I missed something ? thanks.

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()

CodePudding user response：

Apparently, there's no such thing as tables owned by sys available to you (nor me, when we're at it):

SQL> select count(*) from sys.tables;
select count(*) from sys.tables
                         *
ERROR at line 1:
ORA-00942: table or view does not exist

What to do? Use something that is available to you, e.g. your own tables in user_tables:

SQL> select count(*) from user_tables;

  COUNT(*)
----------
        30

or tables accessible to you, owned either by you or someone else:

SQL> select count(*) from all_tables;

  COUNT(*)
----------
       842

SQL>

or, if you are powerful enough, the whole database's tables by querying dba_tables:

SQL> connect sys@pdb1 as sysdba
Enter password:
Connected.
SQL> select count(*) from dba_tables;

  COUNT(*)
----------
      2398

SQL>

Pick one.

As I'm connected as sys right now, what do I see in that tables thing you used?

SQL> select * from tables;
select * from tables
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Still nothing.

So, where exactly did you find information about tables owned by sys you could use for that query?