Error when trying to generateChangeLog : ORA-00942: table or view does not exist

General Discussion
1

I’m trying to generate a change log for a schema on a database.

I get an error, but can’t figure out what the problem might; there is no detail in the message.

The schema contains tables, views, packages, etc

Liquibase Version: 3.3.2
Java: 1.8.0_40

Oracle Database 11g Enterprise Edition, v 11.2.0.4.0

Maybe someone has an idea how to log more details or what might be the problem.

./liquibase --logLevel=debug --driver=oracle.jdbc.OracleDriver --classpath=…\ojdbc6.jar --url=jdbc:oracle:thin:@//zzzzzz:1521/service --username=xyz --password=passwd --changeLogFile=mychangelog.xml generateChangeLog
Liquibase Home is not set.
DEBUG 30-04-15 13:03: liquibase: Connected to xyz@jdbc:oracle:thin:@//zzzzzz:1521/service
DEBUG 30-04-15 13:03: liquibase: Setting auto commit to false from true
DEBUG 30-04-15 13:03: liquibase: Computed checksum for 1430391797690 as dd44bb7b428ad16e2ec12ac83cea2d67
Unexpected error running Liquibase: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

SEVERE 30-04-15 13:03: liquibase: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

liquibase.exception.LiquibaseException: liquibase.command.CommandExecutionException: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at liquibase.integration.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:155)
     at liquibase.integration.commandline.Main.doMigration(Main.java:951)
     at liquibase.integration.commandline.Main.run(Main.java:175)
     at liquibase.integration.commandline.Main.main(Main.java:94)

Caused by: liquibase.command.CommandExecutionException: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at liquibase.command.AbstractCommand.execute(AbstractCommand.java:13)
     at liquibase.integration.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:153)
     ... 3 more

Caused by: liquibase.exception.DatabaseException: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at liquibase.snapshot.jvm.CatalogSnapshotGenerator.snapshotObject(CatalogSnapshotGenerator.java:53)
     at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:60)
     at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:50)
     at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:163)
     at liquibase.snapshot.DatabaseSnapshot.init(DatabaseSnapshot.java:55)
     at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:37)
     at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:25)
     at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:126)
     at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:119)
     at liquibase.command.DiffCommand.createReferenceSnapshot(DiffCommand.java:190)
     at liquibase.command.DiffCommand.createDiffResult(DiffCommand.java:140)
     at liquibase.command.GenerateChangeLogCommand.run(GenerateChangeLogCommand.java:45)
     at liquibase.command.AbstractCommand.execute(AbstractCommand.java:8)
     ... 4 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
     at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
     at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
     at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1275)
     at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
     at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
     at oracle.jdbc.OracleDatabaseMetaData.getSchemas(OracleDatabaseMetaData.java:3105)
     at liquibase.snapshot.jvm.CatalogSnapshotGenerator.getDatabaseCatalogNames(CatalogSnapshotGenerator.java:78)
     at liquibase.snapshot.jvm.CatalogSnapshotGenerator.snapshotObject(CatalogSnapshotGenerator.java:41)
     ... 16 more

For more information, use the --logLevel flag

2

Looking at the code for CatalogSnapshotGenerator.getDatabaseCatalogNames and what calls that, here is what is happening, and what I suspect may be the problem.

  1. In order to generate a changelog, what liquibase does is compare an abstract model of two databases to each other.

  2. In order to generate the abstract models of the databases, it makes a variety of queries to the databases.

  3. One of the queries is to get the names of the catalogs on the database (Different databases have different names for these - Oracle calls these schemas)

  4. When it tries to get the list of schemas, Oracle returns an error that a table doesn’t exist. Since Oracle doesn’t say what table you were querying, it is difficult to tell exactly what is happening, but my suspicion is that the user you are using in the Liquibase connection information doesn’t have sufficient permissions to read the database-level schema information. In general, on Oracle, the user should have the ‘DBA’ role or something pretty close to that.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

1 Like
3

I am not 100% sure that you are having a permissions issue, but it is something to try out - set up a local developer database and give yourself DBA permissions and see if you have the same issue or not.

If it is in fact the case that the problem is with permissions, I’m not sure if there is a workaround or not. It might be that you can instruct Liquibase to only work with the named schema, but I’m not sure. The reason that Liquibase is requiring different permissions than TOAD or sql developer is probably because they are either trying to do fewer/different things, or they are just better at handling the problems.

If you can’t get the necessary permissions, I would suggest setting up a meeting with the appropriate managers to find out ways to get the permissions needed. This may be a symptom of a larger problem that requires changes in several different areas of the company.

Alternatively, ask the DBAs to give you a full dump of the schema in SQL format, apply that to a developer database, and then use Liquibase to generate the XML changelog from that.

Steve Donie
Principal Software Engineer
Datical, Inc. http://www.datical.com/

4

Hi,

thanks for the explanation.

Is there any work-around?

We are in a environment where we can’t get such grants (dba or close is not possible).

Developer tools (toad, sql developer) appear to be able to do without this information, so why does liquibase need it? Just wondering.

/jørgen

5

It turned out to be a permission issue when accessing database metainformation.

So not a liquibase issue but a restrictive corporate setup … and Oracle returning very generic error messages.

Thanks for your help.

/jørgen