Flyway Support Oracle

记录在使用 Flyway 管理 Oracle 数据库脚本时遇到的一些问题,Flyway 5.2.1 - 7.7.3 都存在此问题。

1. Flyway not support Oracle 11g

异常信息

Caused by: org.flywaydb.core.internal.license.FlywayEditionUpgradeRequiredException: Flyway Enterprise Edition or Oracle upgrade required: Oracle 11.2 is no longer supported by Flyway Community Edition, but still supported by Flyway Enterprise Edition.
	at org.flywaydb.core.internal.database.base.Database.ensureDatabaseNotOlderThanOtherwiseRecommendUpgradeToFlywayEdition(Database.java:173)
	at org.flywaydb.core.internal.database.oracle.OracleDatabase.ensureSupported(OracleDatabase.java:91)
	at org.flywaydb.core.Flyway.execute(Flyway.java:514)
	at org.flywaydb.core.Flyway.migrate(Flyway.java:159)
	at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:65)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1855)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1792)
	... 19 common frames omitted

解决办法: 删除 ensureDatabaseNotOlderThanOtherwiseRecommendUpgradeToFlywayEdition 行检查代码

@Override
public final void ensureSupported() {
    ensureDatabaseIsRecentEnough("10");
    ensureDatabaseNotOlderThanOtherwiseRecommendUpgradeToFlywayEdition("12.2", org.flywaydb.core.internal.license.Edition.ENTERPRISE);
    recommendFlywayUpgradeIfNecessary("19.0");
}

2. ALL_SCHEDULER_CREDENTIALS not exist

ALL_SCHEDULER_CREDENTIALS 在 Oracle10 中不存在,并且在 Oracle12c以后废弃

解决办法: 判断 database.getVersion().getMajor().intValue() > 10 才增加 ALL_SCHEDULER_CREDENTIALS 表的关联查询

  /**
   * Returns the schema's existing object types.
   *
   * @return a set of object type names.
   * @throws SQLException if retrieving of object types failed.
   */
  public static Set<String> getObjectTypeNames(JdbcTemplate jdbcTemplate, OracleDatabase database,
      OracleSchema schema) throws SQLException {
    boolean xmlDbAvailable = database.isXmlDbAvailable();

    String query =
        // Most object types can be correctly selected from DBA_/ALL_OBJECTS.
        "SELECT DISTINCT OBJECT_TYPE FROM " + database.dbaOrAll("OBJECTS") + " WHERE OWNER = ? " +
            // Materialized view logs.
            "UNION SELECT '" + MATERIALIZED_VIEW_LOG.getName() + "' FROM DUAL WHERE EXISTS(" +
            "SELECT * FROM ALL_MVIEW_LOGS WHERE LOG_OWNER = ?) " +
            // Dimensions.
            "UNION SELECT '" + DIMENSION.getName() + "' FROM DUAL WHERE EXISTS(" +
            "SELECT * FROM ALL_DIMENSIONS WHERE OWNER = ?) " +
            // Queue tables.
            "UNION SELECT '" + QUEUE_TABLE.getName() + "' FROM DUAL WHERE EXISTS(" +
            "SELECT * FROM ALL_QUEUE_TABLES WHERE OWNER = ?) " +
            // Database links.
            "UNION SELECT '" + DATABASE_LINK.getName() + "' FROM DUAL WHERE EXISTS(" +
            "SELECT * FROM " + database.dbaOrAll("DB_LINKS") + " WHERE OWNER = ?) " +
            // Contexts.
            "UNION SELECT '" + CONTEXT.getName() + "' FROM DUAL WHERE EXISTS(" +
            "SELECT * FROM " + database.dbaOrAll("CONTEXT") + " WHERE SCHEMA = ?) " +
            // XML schemas.
            (xmlDbAvailable
                ? "UNION SELECT '" + XML_SCHEMA.getName() + "' FROM DUAL WHERE EXISTS(" +
                "SELECT * FROM " + database.dbaOrAll("XML_SCHEMAS") + " WHERE OWNER = ?) "
                : "");

    if (database.getVersion().getMajor().intValue() > 10 || !database.getVersion().isAtLeast("12.2")) {
      // Credentials.
      query = query + "UNION SELECT '" + CREDENTIAL.getName() + "' FROM DUAL WHERE EXISTS(" +
          "SELECT * FROM ALL_SCHEDULER_CREDENTIALS WHERE OWNER = ?) ";
    }

    int n = 6 + (xmlDbAvailable ? 1 : 0) +

        1;
    String[] params = new String[n];
    Arrays.fill(params, schema.getName());

    return new HashSet<>(jdbcTemplate.queryForStringList(query, params));
  }

3. oracle_maintained column does not exist

Oracle 12c 之前的版本,ALL_USERS 这表中不存在 ORACLE_MAINTAINED 字段

解决办法: 增加 getVersion().isAtLeast("12.2") 版本判断,当小于 12.2 版本时不判断 ORACLE_MAINTAINED 字段

/**
 * Returns the list of schemas that were created and are maintained by Oracle-supplied scripts and must not be
 * changed in any other way. The list is composed of default schemas mentioned in the official documentation for
 * Oracle Database versions from 10.1 to 12.2, and is dynamically extended with schemas from DBA_REGISTRY and
 * ALL_USERS (marked with ORACLE_MAINTAINED = 'Y' in Oracle 12c).
 *
 * @return the set of system schema names
 */
Set<String> getSystemSchemas() throws SQLException {

  // The list of known default system schemas
  Set<String> result = new HashSet<>(Arrays.asList(
      "SYS", "SYSTEM", // Standard system accounts
      "SYSBACKUP", "SYSDG", "SYSKM", "SYSRAC", "SYS$UMF", // Auxiliary system accounts
      "DBSNMP", "MGMT_VIEW", "SYSMAN", // Enterprise Manager accounts
      "OUTLN", // Stored outlines
      "AUDSYS", // Unified auditing
      "ORACLE_OCM", // Oracle Configuration Manager
      "APPQOSSYS", // Oracle Database QoS Management
      "OJVMSYS", // Oracle JavaVM
      "DVF", "DVSYS", // Oracle Database Vault
      "DBSFWUSER", // Database Service Firewall
      "REMOTE_SCHEDULER_AGENT", // Remote scheduler agent
      "DIP", // Oracle Directory Integration Platform
      "APEX_PUBLIC_USER", "FLOWS_FILES", /*"APEX_######", "FLOWS_######",*/
      // Oracle Application Express
      "ANONYMOUS", "XDB", "XS$NULL", // Oracle XML Database
      "CTXSYS", // Oracle Text
      "LBACSYS", // Oracle Label Security
      "EXFSYS", // Oracle Rules Manager and Expression Filter
      "MDDATA", "MDSYS", "SPATIAL_CSW_ADMIN_USR", "SPATIAL_WFS_ADMIN_USR",
      // Oracle Locator and Spatial
      "ORDDATA", "ORDPLUGINS", "ORDSYS", "SI_INFORMTN_SCHEMA", // Oracle Multimedia
      "WMSYS", // Oracle Workspace Manager
      "OLAPSYS", // Oracle OLAP catalogs
      "OWBSYS", "OWBSYS_AUDIT", // Oracle Warehouse Builder
      "GSMADMIN_INTERNAL", "GSMCATUSER", "GSMUSER", // Global Data Services
      "GGSYS", // Oracle GoldenGate
      "WK_TEST", "WKSYS", "WKPROXY", // Oracle Ultra Search
      "ODM", "ODM_MTR", "DMSYS", // Oracle Data Mining
      "TSMSYS" // Transparent Session Migration
  ));

  if (!getVersion().isAtLeast("12.2")) {
    result.addAll(
        getMainConnection().getJdbcTemplate()
            .queryForStringList("SELECT USERNAME FROM ALL_USERS " +
                "WHERE REGEXP_LIKE(USERNAME, '^(APEX|FLOWS)_\\d+$')"
            ));
  } else {
    result.addAll(
        getMainConnection().getJdbcTemplate()
            .queryForStringList("SELECT USERNAME FROM ALL_USERS " +
                "WHERE REGEXP_LIKE(USERNAME, '^(APEX|FLOWS)_\\d+$')" +
                " OR ORACLE_MAINTAINED = 'Y'"
            ));
  }
  return result;
}