ORA-01732: 此视图的数据操纵操作非法

问题描述

oracle数据库有创建materialized views，备份成功，恢复时出现警告：

..正在导入表     “TESTMAVIEW”IMP-00058: 遇到 ORACLE 错误 1732ORA-01732: 此视图的数据操纵操作非法 

备注：
执行恢复前未删除materialized views，数据库恢复失败；
删除materialized views，数据库恢复成功。

原因

物化视图可像表一样存储数据，通过pl/sql查看对象列表时发现创建的物化视图在tables对象列中，而通过drop语句直接删除物化视图对象报错：

ORA-12003:必须用DROP MATERIALIZED VIEW 来删除物化视图。

ps:因为是通过程序进行数据库备份，所以会先删除数据库中存在的表及其他对象，然后调用imp命令进行数据库导入。

解决方法

先通过调用DROP MATERIALIZED VIEW mv_name 删除所有的物化视图，再删除其他表对象。

代码

protected ConnectionConfBuilder connectionConfBuilder;protected Connection connection;/**   * @Title: romveObject   * @Description: 删除数据库对象   * @param dbType   * @throws EbeimException   * @author zxk   * @version 1.0   */public void romveObject(String dbType) throws EbeimException {    // 创建连接解析器    this.connectionConfBuilder = IOCUtils.getBean(ConnectionConfBuilderUtils.getConnectionConfBuilderName(dbType));    this.connectionConfBuilder.initialize(super.dbConfig);    // 删除MATERIALIZED VIEW    this.dropMaterializedView(2, "MATERIALIZED VIEW");}/**   * @Title: dropMaterializedView   * @Description: 删除dropMaterialized View   * @param count   * @throws EbeimException   * @author zxk   * @version 1.0   */ private void dropMaterializedView(int count, String objectType) throws EbeimException {    ResultSet rs = null;    PreparedStatement pStatement = null;    PreparedStatement dropStatement = null;    try {      this.getConnection(count);      pStatement = this.connection          .prepareStatement("select OBJECT_NAME from user_objects where object_type='" + objectType + "'");      rs = pStatement.executeQuery();      while (rs.next()) {        String tt = rs.getString("OBJECT_NAME"); // 获取对象名        try {          String dropsql = "DROP MATERIALIZED VIEW " + tt;          dropStatement = this.connection.prepareStatement(dropsql);          dropStatement.executeUpdate();        } catch (SQLException e1) {          LOG.error("删除[{}]失败", tt, e1);          if (dropStatement != null) {            dropStatement.close();          }        }        dropStatement.close();      }    } catch (Exception e) {      throw new EbeimException("删除" + objectType + "失败" + "-->" + e.getMessage(), e);    } finally {      try {        if (rs != null) {          rs.close();        }        if (pStatement != null) {          pStatement.close();        }      } catch (Exception e) {        LOG.error("关闭数据库连接失败");      }    }}/**   * @Title: getConnection   * @Description:获取连接   * @param count   * @throws EbeimException   * @version 1.0   */ private void getConnection(int count) throws EbeimException {    try {      Driver driver = this.connectionConfBuilder.getDriver();      String url = this.connectionConfBuilder.getUrl().replaceAll("%", "%25");      Properties info = new Properties();      info.setProperty("user", connectionConfBuilder.getUserName());      info.setProperty("password", connectionConfBuilder.getPassword());      this.connection = driver.connect(url, info);    } catch (Exception e) {      count = count - 1;      if (count <= 0) {        throw new EbeimException("获取数据库连接失败" + e.getMessage(), e);      }      // 休眠      try {        // 休眠5秒钟后重新获取        Thread.sleep(5000);      } catch (InterruptedException ex) {      }      getConnection(count);    }  }

---