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

问题描述

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

..正在导入表     “TESTMAVIEW”
IMP-00058: 遇到 ORACLE 错误 1732
ORA-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 Exception
   * @author zxk
   * @version 1.0
   */
public void romveObject(String dbType) throws Exception {
    // 创建连接解析器
    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 Exception
   * @author zxk
   * @version 1.0
   */
 private void dropMaterializedView(int count, String objectType) throws Exception {
    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 Exception("删除" + 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 Exception
   * @version 1.0
   */
 private void getConnection(int count) throws Exception {
    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 Exception("获取数据库连接失败" + e.getMessage(), e);
      }
      // 休眠
      try {
        // 休眠5秒钟后重新获取
        Thread.sleep(5000);
      } catch (InterruptedException ex) {

      }
      getConnection(count);
    }
  }

---