强制Oracle删除全局临时表 [英] Force Oracle Drop Global Temp Table

查看：165
本文介绍了强制Oracle删除全局临时表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

在我们的项目中，我将创建一些类似于以下内容的全局临时表:

In our project I create some global temp table that will be like these:

CREATE GLOBAL TEMPORARY TABLE v2dtemp (
  id           NUMBER,
  GOOD_TYPE_GROUP       VARCHAR2(250 BYTE),
  GOOD_CODE             VARCHAR2(50 BYTE),
  GOOD_TITLE            VARCHAR2(250 BYTE)
)
ON COMMIT PRESERVE ROWS;

但是问题出在我要删除该表时. Oracle不会让我放下桌子，它说:

but the problem comes when I want to drop this table. Oracle will not let me to drop the table, and it says:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

我必须在某些过程中使用此表，但是它可能会根据其他报告而更改.因此，我应该始终删除表，然后使用所需的字段重新创建它.

I have to use this table in some procedure but it may be changed dependent to other reports. So I should always drop the table then I should recreate it with my needed fields.

出于某些业务原因，我必须使用此功能，因此我无法使用表或其他东西.我可以只使用临时表. 我尝试了提交删除行，但是当我调用过程以使用该表中的数据时，表中没有更多行，它们已被删除.

I have to use this for some business reasons so it is not possible for me to use tables, or other things. I can use just temp tables. I tried on commit delete rows, but when I call my procedure to use the data in this table there are no more rows in the table and they have been deleted.

任何帮助将不胜感激， 预先感谢

Any helps will greatly appreciated, thanks in advance

///编辑

public void saveJSONBatchOpenJobs(final JSONArray array, MtdReport report) {
    dropAndCreateTable();
    String sql = "INSERT INTO v2d_temp " +
            "(ID, KARPARDAZ, GOOD_TYPE_GROUP, GOOD_CODE, GOOD_TITLE, COUNT, "
            + "FACTOR_COUNT, GHABZ_COUNT, DEAL_NO, DEAL_DATE, REQUEST_NO, REQUEST_DATE, "
            + "REQUEST_CLIENT, STATUS, TYPE, MTDREPORT_ID, GEN_SECURITY_DATA_ID) " +
            "VALUES (MTD_KARPARDAZ_OPEN_JOBS_SEQ.nextval,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            JSONArray values = array.getJSONArray(i);
            if(!values.get(0).equals("null"))
                ps.setString(1, values.get(0).toString());
            else
                ps.setNull(1, Types.VARCHAR);
            if(!values.get(1).equals("null"))
                ps.setString(2, values.get(1).toString());
            else
                ps.setNull(2, Types.VARCHAR);
            if(!values.get(2).equals("null"))
                ps.setString(3, values.get(2).toString());
            else
                ps.setNull(3, Types.VARCHAR);
            if(!values.get(3).equals("null"))
                ps.setString(4, values.get(3).toString());
            else
                ps.setNull(4, Types.VARCHAR);
            if(!values.get(4).equals("null"))
                ps.setBigDecimal(5, new BigDecimal(values.get(4).toString()));
            else
                ps.setNull(5, Types.NUMERIC);
            if(!values.get(5).equals("null"))
                ps.setBigDecimal(6, new BigDecimal(values.get(5).toString()));
            else
                ps.setNull(6, Types.NUMERIC);
            if(!values.get(6).equals("null"))
                ps.setBigDecimal(7, new BigDecimal(values.get(6).toString()));
            else
                ps.setNull(7, Types.NUMERIC);
            if(!values.get(7).equals("null"))
                ps.setString(8, values.get(7).toString());
            else
                ps.setNull(8, Types.VARCHAR);
            if(!values.get(8).equals("null"))
                ps.setDate(9, new Date(new Timestamp(values.getLong(8)).getDateTime()));
            else
                ps.setNull(9, Types.DATE);
            if(!values.get(9).equals("null"))
                ps.setString(10, values.get(9).toString());
            else
                ps.setNull(10, Types.VARCHAR);
            if(!values.get(10).equals("null"))
                ps.setDate(11, new Date(new Timestamp(values.getLong(8)).getDateTime()));
            else
                ps.setNull(11, Types.DATE);
            if(!values.get(11).equals("null"))
                ps.setString(12, values.get(11).toString());
            else
                ps.setNull(12, Types.VARCHAR);
            if(!values.get(12).equals("null"))
                ps.setString(13, values.get(12).toString());
            else
                ps.setNull(13, Types.VARCHAR);
            if(!values.get(13).equals("null"))
                ps.setString(14, values.get(13).toString());
            else
                ps.setNull(14, Types.VARCHAR);
            if(!values.get(14).equals("null"))
                ps.setLong(15, new Long(values.get(14).toString()));
            else
                ps.setNull(15, Types.NUMERIC);
            if(!values.get(15).equals("null"))
                ps.setLong(16, new Long(values.get(15).toString()));
            else
                ps.setNull(16, Types.NUMERIC);
        }

        @Override
        public int getBatchSize() {
            return array.size();
        }
    });

    String bulkInsert = "declare "
            + "type array is table of d2v_temp%rowtype;"
            + "t1 array;"
            + "begin "
            + "select * bulk collect into t1 from d2v_temp;"
            + "forall i in t1.first..t1.last "
            + "insert into vertical_design values t1(i);"
            + "end;";
    executeSQL(bulkInsert);
}

private void dropAndCreateTable() {
    String dropSql = "declare c int;"
            + "begin "
            + "select count(*) into c from user_tables where table_name = upper('v2d_temp');"
            + "if c = 1 then "
            + "truncate table v2d_temp"
            + "drop table v2d_temp;"
            + " end if;"
            + "end;";
    executeSQL(dropSql);

    String createSql = "CREATE GLOBAL TEMPORARY TABLE v2d_temp (\n"
            + "DEAL_ID               NUMBER,\n"
            + "id           NUMBER,\n"
            + "karpardaz  VARCHAR2(350),\n"
            + "GOOD_TYPE_GROUP       VARCHAR2(250 BYTE),\n"
            + "GOOD_CODE             VARCHAR2(50 BYTE),\n"
            + "GOOD_TITLE            VARCHAR2(250 BYTE),\n"
            + "COUNT                 NUMBER,\n"
            + "FACTOR_COUNT          NUMBER,\n"
            + "GHABZ_COUNT           NUMBER,\n"
            + "DEAL_NO               VARCHAR2(50 BYTE),\n"
            + "DEAL_DATE             DATE,\n"
            + "REQUEST_NO            VARCHAR2(50 BYTE),\n"
            + "REQUEST_DATE          DATE,\n"
            + "REQUEST_CLIENT        VARCHAR2(250 BYTE),\n"
            + "STATUS                VARCHAR2(250 BYTE),\n"
            + "TYPE                  VARCHAR2(250 BYTE),\n"
            + "GEN_SECURITY_DATA_ID  NUMBER(10),\n"
            + "MTDREPORT_ID          NUMBER\n"
            + ")\n"
            + "ON COMMIT PRESERVE ROWS";
    executeSQL(createSql);
}

private void executeSQL(String sql) {
    Connection con = null;
    try {
        con = getConnection();
        Statement st = con.createStatement();
        st.execute(sql);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if(con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

推荐答案

Oracle全局临时表不是临时对象.它们是正确的堆表.我们创建它们一次，任何会话都可以使用它们存储仅对该会话可见的数据.

Oracle global temporary tables are not transient objects. They are proper heap tables. We create them once and any session can use them to store data which is visible only to that session.

临时的方面是，数据不能超出一个事务或一个会话的持久性.关键的实现细节是将数据写入临时表空间而不是永久表空间.但是，数据仍会写入磁盘或从磁盘读取，因此使用全局临时表会有明显的开销.

The temporary aspect is that the data is not persistent beyond one transaction or one session. The key implementation detail is that the data is written to a temporary tablespace not a permanent one. However, the data is still written to - and read from - disk, so there is a notable overhead to the use of global temporary tables.

重点是我们不应删除并重新创建临时表.如果试图将SQL Server样式逻辑移植到Oracle中，则应考虑使用PL/SQL集合来维护内存中的临时数据. 了解更多信息.

The point is we are not supposed to drop and recreate temporary tables. If you're trying to port SQL Server style logic into Oracle then you should consider using PL/SQL collections to maintain temporary data in-memory. Find out more.

ORA-14452的特定原因是，如果会话期间包含数据，则我们不能删除具有会话范围持久性的全局临时表.即使表当前为空...

The specific cause of ORA-14452 is that we cannot drop a global temporary table which has session scope persistence if it has contained data during the session. Even if the table is currently empty...

SQL> create global temporary table gtt23 (col1 number)
  2  on commit preserve rows
  3  /

Table created.

SQL> insert into gtt23 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> delete from gtt23;

1 row deleted.

SQL> commit;

Commit complete.

SQL> drop table gtt23;
drop table gtt23
           *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

SQL>

解决方案是结束会话并重新连接，或者(有点奇怪)截断表然后将其删除.

The solution is to end the session and re-connect, or (somewhat bizarrely) to truncate the table and then drop it.

SQL> truncate table gtt23;

Table truncated.

SQL> drop table gtt23;

Table dropped.

SQL>

如果其他某个会话正在使用全局临时表-并且这是可能的(因此 global 命名法)，那么您将无法删除该表，直到所有会话都断开连接.

If some other session is using the global temporary table - and that is possible (hence the global nomenclature) then you won't be able to drop the table until all the sessions disconnect.

因此，真正的解决方案是学习正确使用全局临时表:创建特定的全局临时表以匹配每个报表.或者，就像我说的那样，改用PL/SQL集合.或者，甚至，只是学习编写经过良好调整的SQL.通常，我们将临时表用作写得不好的查询的解决方法，该查询可以通过更好的访问路径进行保存.

So the real solution is to learn to use global temporary tables properly: create specific global temporary tables to match each report. Or, as I say, use PL/SQL collections instead. Or, even, just learn to write well-tuned SQL. Often we use temporary tables as a workaround to a poorly-written query which could be saved with a better access path.

查看了完整的代码后，流程似乎更加奇怪:

Having looked at your full code, the flow seems even more bizarre:

  1. 拖放并重新创建全局临时表
  2. 填充临时表
  3. 从临时表中选择PL/SQL数组
  4. 使用PL/SQL数组中的批量插入功能插入实际表中

这里有太多的开销和浪费的活动.您所需要做的就是将插入到v2d_temp中的数据直接填充到vertical_design中，最好使用INSERT INTO ... SELECT * FROM语句.您将需要进行一些预处理才能将JSON数组转换为查询，但这在Java或PL/SQL中都很容易实现.

There's so much overhead and wasted activity in here. All you need to do is take the data you insert into v2d_temp and directly populate vertical_design, ideally with an INSERT INTO ... SELECT * FROM statement. You will require some pre-processing to convert a JSON array into a query but that is easy to achieve in either Java or PL/SQL.

在我看来，全局临时表不是您的方案的正确解决方案.

It seems certain to me that global temporary tables are not the right solution for your scenario.

我们的老板或其他人坚持通过自己的方式做某事，所以您不能改变它"

"our boss or other persons persist to do something through their way, so you cannot change that"

您遇到的是一个老板问题，而不是一个编程问题.因此，就StackOverflow而言，它是离题的.但是无论如何，这里有一些建议.

What you have is a Boss Problem not a Programming Problem. Consequently it is off-topic as far as StackOverflow goes. But here are some suggestions anyway.

要记住的关键是，我们并不是在谈论某些次优架构的折衷方案:您的老板明确提出的建议在多用户环境中是行不通的.因此，您的选择是:

The key thing to remember is that we are not talking about a compromise on some sub-optimal architecture: what your boss proposes clearly won't work in a multi-user environment. so, your options are:

  1. 忽略ORA-14452错误，进入生产阶段，然后在所有出现严重错误的情况下使用但您已告诉我"防御.这是最弱的游戏.
  2. 将全局表换成垃圾，并实现在多用户方案中可以使用的功能.这是高风险的，因为如果破坏实现，您将无法防御.
  3. 与老板交谈.告诉他们您遇到了ORA-14452错误，说您已经进行了调查，以这种方式使用全局临时表似乎是一个基本问题，但显然您已忽略了一些事情.然后，询问他们在以前实施该问题时如何解决该问题.这可以有几种方法，也许他们有一个解决方法，也许他们会意识到这是使用全局临时表的错误方法，也许他们会告诉您迷路.无论哪种方式，这都是最好的方法:您已经将关注点提高到了适当的水平.
  1. Ignore the ORA-14452 error, proceed into production and then use the "but you told me to" defence when it all goes horribly wrong. This is the weakest play.
  2. Covertly junk the global tables and implement something which will work in a multi-user scenario. This is high-risk because you have no defence if you botch the implementation.
  3. Speak to your boss. Tell them you're running into the ORA-14452 error, say you have done some investigation and it appears to a fundamental issue with using global temporary tables in this fashion but obviously you've overlooked something. Then, ask them how they got around this problem when they've implemented it before. This can go several ways, maybe they have a workaround, maybe they'll realise that this is the wrong way to use global temporary tables, maybe they'll tell you to get lost. Either way, this is the best approach: you've raised concerns to the appropriate level.

祝你好运.

这篇关于强制Oracle删除全局临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆