Oracle Materialized views DROP

Hi all,

In our project, we need to implement materialized views. Creating them using SQL with Liquibase is no problem, but it was failing when recreating the schema.

All materialized views are seen as TABLEs, so when Liquibase runs the “dropAll” job, the following SQL is called, and fails:


it should be:


Here’s what I had to do to solve the issue:

  1. extends OracleDatabase to override the “dropDatabaseObjects” method.

  2. create DropMaterializedView Satement, Generator and Change.

  3. add my package in the MANIFEST.MF of my plugin JAR so the OracleDatabase would be chosen by the factory.

To make it more simple, maybe we could include my code in one of the releases?

In a nutshell, here it is:

  1.     private static final Logger logger = LogFactory.getLogger();

  2.     public OracleDatabase() {
  3.         super();
  4.         SqlGeneratorFactory.getInstance().register(new DropMaterializedViewGenerator());
  5.     }

  6.     @Override
  7.     public int getPriority() {
  8.         return PRIORITY_DATABASE;
  9.     }

  10.     @Override
  11.     public void dropDatabaseObjects(String schema) throws DatabaseException {
  12."## custom Oracle DataBase drop support ##");

  13.         List mviews = this.retrieveMViewNames();
  14.         List changes = new ArrayList(mviews.size());
  15.         for (String name : mviews) {
  16.             DropMaterializedView ch = new DropMaterializedView();
  17.             ch.setViewName(name);
  18.             ch.setSchemaName(schema);
  19.             changes.add(ch);
  20.         }
  21.         for (Change change : changes) {
  22.             for (SqlStatement statement : change.generateStatements(this)) {
  23.                 ExecutorService.getInstance().getExecutor(this).execute(statement);
  24.             }
  25.         }

  26.         super.dropDatabaseObjects(schema);
  27.     }

  28.     private List retrieveMViewNames() {
  29.         List mviews = new ArrayList();
  30.         Statement st = null;
  31.         ResultSet rs = null;
  32.         try {
  33.             st = ((JdbcConnection) this.getConnection()).getUnderlyingConnection().createStatement();
  34.             rs = st.executeQuery("select MVIEW_NAME from all_mviews");

  35.             while ( {
  36.                 String mview = rs.getString(1);
  37.                 logger.debug("found MVIEW: " + mview);
  38.                 mviews.add(mview);
  39.             }
  40.         }
  41.         catch (SQLException e) {
  42.             e.printStackTrace();
  43.         } finally {
  44.             try {
  45.                 if (rs != null) {
  46.                     rs.close();
  47.                 }
  48.                 if (st != null) {
  49.                     st.close();
  50.                 }
  51.             }
  52.             catch (SQLException e) {
  53.                 e.printStackTrace();
  54.             }
  55.         }
  56.         return mviews;
  57.     }

For the statement and generators, the same as the DropView can be implemented, with the generator returning "DROP MATERIALIZED VIEW " + args.

Maybe there is a better way of retrieving the MViews than querying a table. I’ll look into it if my projects leaves me time for that.

Thanks for the great tool,



It's a pitty this can't be achieved through liquibase itself so I worked around it using sql-maven-plugin to drop all materialized views before liquibase kicks in and only when liquibase.dropFirst is set to true. This avoids custom code and messing with classpath.

Used maven profiles to set a property to be the inverse boolean of another one since maven doesn't support it out of the box. I guess you could put the whole plugin execution in the profile too. It would probably be neater to also externalise the sql in the pom to an sql file, but for purposes of simple answer it's all below.

 My pom.xml

                                      type mv_names_t is table of all_mviews.mview_name%type index by pls_integer;
                                      mview_names mv_names_t;
                                      select mview_name
                                      bulk collect into mview_names
                                      from all_mviews;
  3.                                   for indx in 1…mview_names.count loop
                                        execute immediate 'drop materialized view ’ || mview_names(indx);
                                      end loop;
  4.     …
          By the way the editor posting on this site is quite buggy - I had to use my windows VM to reply in IE and even then the rendering was all off. Editor didn’t even appear in chrome and ff on ubuntu.

        I created to track the feature. It will probably go into the oracle extension since it is fairly oracle specific at this point.