ORA-65040 error when trying to clean different schema with system user

[hamiller]

A simple "flyway clean" leads to an error ORA-65040 when trying to execute it against a Oracle 12c database - both on linux.

Database Version:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
"CORE   12.1.0.1.0  Production"
TNS for Linux: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

Output Log:

./flyway -X clean


Flyway 3.2.1 by Boxfuse

DEBUG: Unable to load config file: xxx/flyway-3.2.1/conf/flyway.properties
DEBUG: Loading config file: xxx/flyway-3.2.1/conf/flyway.conf
DEBUG: Unable to load config file: /home/yyy/flyway.conf
DEBUG: Unable to load config file: xxx/flyway-3.2.1/flyway.conf
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/derby-10.11.1.1.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/jna-3.3.0.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/ojdbc6.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/h2-1.3.170.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/postgresql-9.3-1102-jdbc4.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/hsqldb-2.3.2.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/jna-3.3.0-platform.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/mariadb-java-client-1.1.7.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/derbyclient-10.11.1.1.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/sqlite-jdbc-3.7.15-M1.jar
DEBUG: Adding location to classpath: xxx/flyway-3.2.1/drivers/jtds-1.3.1.jar
Database: jdbc:oracle:thin:@//localhost:1521/icsora (Oracle 12.1)
DEBUG: DDL Transactions Supported: false
DEBUG: Schema: ICSNT
DEBUG: Scanning for filesystem resources at 'xxx/flyway-3.2.1/sql' (Prefix: '', Suffix: '.sql')
DEBUG: Scanning for resources in path: xxx/flyway-3.2.1/sql (xxx/flyway-3.2.1/sql)
DEBUG: Filtering out resource: xxx/flyway-3.2.1/sql/put-your-sql-migrations-here.txt (filename: put-your-sql-migrations-here.txt)
DEBUG: Cleaning schema "ICSNT" ...
DEBUG: Rolling back transaction...
DEBUG: Transaction rolled back
ERROR: Unexpected error
org.flywaydb.core.api.FlywayException: Unable to clean schema "ICSNT"
    at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:150)
    at org.flywaydb.core.internal.command.DbClean$4.doInTransaction(DbClean.java:154)
    at org.flywaydb.core.internal.command.DbClean$4.doInTransaction(DbClean.java:152)
    at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:72)
    at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:152)
    at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:106)
    at org.flywaydb.core.Flyway$3.execute(Flyway.java:1125)
    at org.flywaydb.core.Flyway$3.execute(Flyway.java:1121)
    at org.flywaydb.core.Flyway.execute(Flyway.java:1418)
    at org.flywaydb.core.Flyway.clean(Flyway.java:1121)
    at org.flywaydb.commandline.Main.executeOperation(Main.java:115)
    at org.flywaydb.commandline.Main.main(Main.java:91)
Caused by: java.sql.SQLException: ORA-65040: Vorgang ist innerhalb einer integrierbaren Datenbank nicht zulässig
ORA-06512: in "SYS.DBMS_AQADM", Zeile 388
ORA-06512: in Zeile 1

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
    at org.flywaydb.core.internal.dbsupport.JdbcTemplate.execute(JdbcTemplate.java:219)
    at org.flywaydb.core.internal.dbsupport.oracle.OracleSchema.doClean(OracleSchema.java:82)
    at org.flywaydb.core.internal.dbsupport.Schema.clean(Schema.java:148)
    ... 11 more

[axelfontaine]

Your user has queue tables. You need to GRANT EXECUTE ON DBMS_AQADM TO <youruser> for Flyway to be able to clean them. I've improved the error output to make this clearer.

[hamiller]

I don't think this is correct: I'm trying to get rid of the schema as the system user.
Nevertheless, granting the privilege to the schema user doesn't resolve the problem either.

[axelfontaine]

Please post your full Flyway config so we can clear this up.

[hamiller]

flyway.url=jdbc:oracle:thin:@//localhost:1521/icsora
flyway.user=system
flyway.password=***
flyway.schemas=ICSNT

[hamiller]

the config is working fine if I don't clean the schema befor (migrating works)

[axelfontaine]

Should be fixed now. Please test the latest code to make sure your case is fully covered.