强制 Oracle 删除全局临时表
在我们的项目中，我创建了一些全局临时表，如下所示:
创建全局临时表 v2dtemp (身份证号码，GOOD_TYPE_GROUP VARCHAR2(250 字节)，GOOD_CODE VARCHAR2(50 字节)，GOOD_TITLE VARCHAR2(250 字节))提交保留行；但是当我想删除这个表时问题就来了.Oracle 不会让我删除表，它说:
ORA-14452:尝试在已使用的临时表上创建、更改或删除索引我必须在某些过程中使用此表，但它可能会根据其他报告进行更改.所以我应该总是删除表格，然后我应该用我需要的字段重新创建它.
出于某些业务原因，我必须使用它，因此我无法使用表格或其他东西.我只能使用临时表.我尝试提交删除行，但是当我调用我的过程以使用此表中的数据时，表中没有更多行并且它们已被删除.
任何帮助将不胜感激，提前致谢
///编辑
public void saveJSONBatchOpenJobs(final JSONArray array, MtdReport report) {dropAndCreateTable();字符串 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、状态、类型、MTDREPORT_ID、GEN_SECURITY_DATA_ID)" +"值 (MTD_KARPARDAZ_OPEN_JOBS_SEQ.nextval,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {@覆盖public void setValues(PreparedStatement ps, int i) 抛出 SQLException {JSONArray 值 = array.getJSONArray(i);if(!values.get(0).equals("null"))ps.setString(1, values.get(0).toString());别的ps.setNull(1, Types.VARCHAR);if(!values.get(1).equals("null"))ps.setString(2, values.get(1).toString());别的ps.setNull(2, Types.VARCHAR);if(!values.get(2).equals("null"))ps.setString(3, values.get(2).toString());别的ps.setNull(3, Types.VARCHAR);if(!values.get(3).equals("null"))ps.setString(4, values.get(3).toString());别的ps.setNull(4, Types.VARCHAR);if(!values.get(4).equals("null"))ps.setBigDecimal(5, new BigDecimal(values.get(4).toString()));别的ps.setNull(5, Types.NUMERIC);if(!values.get(5).equals("null"))ps.setBigDecimal(6, new BigDecimal(values.get(5).toString()));别的ps.setNull(6, Types.NUMERIC);if(!values.get(6).equals("null"))ps.setBigDecimal(7, new BigDecimal(values.get(6).toString()));别的ps.setNull(7, Types.NUMERIC);if(!values.get(7).equals("null"))ps.setString(8, values.get(7).toString());别的ps.setNull(8, Types.VARCHAR);if(!values.get(8).equals("null"))ps.setDate(9, new Date(new Timestamp(values.getLong(8)).getDateTime()));别的ps.setNull(9, Types.DATE);if(!values.get(9).equals("null"))ps.setString(10, values.get(9).toString());别的ps.setNull(10, Types.VARCHAR);if(!values.get(10).equals("null"))ps.setDate(11, new Date(new Timestamp(values.getLong(8)).getDateTime()));别的ps.setNull(11, Types.DATE);if(!values.get(11).equals("null"))ps.setString(12, values.get(11).toString());别的ps.setNull(12, Types.VARCHAR);if(!values.get(12).equals("null"))ps.setString(13, values.get(12).toString());别的ps.setNull(13, Types.VARCHAR);if(!values.get(13).equals("null"))ps.setString(14, values.get(13).toString());别的ps.setNull(14, Types.VARCHAR);if(!values.get(14).equals("null"))ps.setLong(15, new Long(values.get(14).toString()));别的ps.setNull(15, Types.NUMERIC);if(!values.get(15).equals("null"))ps.setLong(16, new Long(values.get(15).toString()));别的ps.setNull(16, Types.NUMERIC);}@覆盖公共 int getBatchSize() {返回数组大小()；}});String bulkInsert = "声明"+ "类型数组是 d2v_temp%rowtype 的表；"+ "t1 数组;"+开始"+ "select * 从 d2v_temp 批量收集到 t1;"+ "forall i in t1.first..t1.last "+ "插入vertical_design 值t1(i);"+ "结束;";执行SQL(批量插入)；}私有无效 dropAndCreateTable() {String dropSql = "声明c int;"+开始"+ "select count(*) into c from user_tables where table_name = upper('v2d_temp');"+ "如果 c = 1 那么 "+ "截断表 v2d_temp"+ "删除表 v2d_temp;"+ " 如果结束;"+ "结束;";执行SQL(dropSql);String createSql = "创建全局临时表 v2d_temp (
"+ "交易 ID 号，
"+ "身份证号码，
"+ "karpardaz VARCHAR2(350),
"+ "GOOD_TYPE_GROUP VARCHAR2(250 BYTE),
"+ "GOOD_CODE VARCHAR2(50 BYTE),
"+ "GOOD_TITLE VARCHAR2(250 BYTE),
"+ "计数编号，
"+ "FACTOR_COUNT NUMBER，
"+ "GHABZ_COUNT NUMBER，
"+ "DEAL_NO VARCHAR2(50 BYTE),
"+ "DEAL_DATE 日期，
"+ "REQUEST_NO VARCHAR2(50 BYTE),
"+ "REQUEST_DATE 日期，
"+ "REQUEST_CLIENT VARCHAR2(250 BYTE),
"+ "状态 VARCHAR2(250 字节),
"+ "类型 VARCHAR2(250 字节),
"+ "GEN_SECURITY_DATA_ID NUMBER(10),
"+ "MTDREPORT_ID 号
"+ ")
"+提交保留行"；执行SQL(createSql);}私有无效executeSQL(字符串sql){连接 con = null;尝试 {con = getConnection();语句 st = con.createStatement();st.execute(sql);} catch (SQLException e) {e.printStackTrace();} 最后 {if(con != null) {尝试 {关闭()；} catch (SQLException e) {e.printStackTrace();}}}}Oracle 全局临时表不是临时对象.它们是合适的堆表.我们创建它们一次，任何会话都可以使用它们来存储只有那个会话可见的数据.
临时方面是数据不会在一个事务或一个会话之外持久化.关键的实现细节是将数据写入临时表空间而不是永久表空间.然而，数据仍然写入-和读取-磁盘，因此使用全局临时表有显着的开销.
关键是我们不应该删除和重新创建临时表.如果您尝试将 SQL Server 风格的逻辑移植到 Oracle 中，那么您应该考虑使用 PL/SQL 集合来维护内存中的临时数据.了解更多信息.
ORA-14452 的具体原因是我们不能删除具有会话范围持久性的全局临时表，如果它在会话期间包含数据.即使表当前是空的...
SQL>创建全局临时表gtt23(col1号)2 提交保留行3/表已创建.SQL>插入gtt23值(1)；已创建 1 行.SQL>犯罪;提交完成.SQL>从gtt23中删除；已删除 1 行.SQL>犯罪;提交完成.SQL>删除表gtt23；删除表gtt23*第 1 行的错误:ORA-14452: 尝试在已使用的临时表上创建、更改或删除索引SQL>解决方案是结束会话并重新连接，或者(有点奇怪)截断表然后删除它.
SQL>截断表gtt23；表被截断.SQL>删除表gtt23；桌子掉下来.SQL>如果某个其他会话正在使用全局临时表 - 这是可能的(因此是全局命名法)，那么在所有会话断开连接之前，您将无法删除该表.
所以真正的解决办法是学会正确使用全局临时表:创建特定的全局临时表来匹配每个报表.或者，正如我所说，改用 PL/SQL 集合.或者，甚至，只是学习编写经过良好调整的 SQL.通常，我们使用临时表来解决写得不好的查询，可以使用更好的访问路径来保存它.
<小时>查看您的完整代码后，流程似乎更加奇怪:
- 删除并重新创建一个全局临时表
- 填充临时表
- 从临时表中选择到 PL/SQL 数组
- 使用来自 PL/SQL 数组的批量插入插入实际表
这里有太多的开销和浪费的活动.您需要做的就是将您插入的数据放入 v2d_temp 并直接填充 vertical_design，最好使用 INSERT INTO ... SELECT * FROM 语句.您需要进行一些预处理才能将 JSON 数组转换为查询，但这在 Java 或 PL/SQL 中很容易实现.
在我看来，全局临时表不是适合您的方案的正确解决方案.
<小时><块引用>我们的老板或其他人坚持按照他们的方式做某事，所以你无法改变这一点"
您遇到的是老板问题，而不是编程问题.因此，就 StackOverflow 而言，它是题外话.但无论如何，这里有一些建议.
要记住的关键是，我们不是在谈论对某些次优架构的妥协:您的老板明确提出的建议在多用户环境中行不通.因此，您的选择是:
- 忽略
ORA-14452错误，进入生产环境，然后在出现严重错误时使用但你告诉我要"防御.这是最弱的打法. - 秘密地丢弃全局表并实施一些可以在多用户场景中工作的东西.这是高风险的，因为如果你搞砸了实施，你就没有防御能力.
- 和你的老板谈谈.告诉他们您遇到了
ORA-14452错误，假设您已经进行了一些调查，并且以这种方式使用全局临时表似乎是一个基本问题，但显然您忽略了一些东西.然后，询问他们以前实施过这个问题时是如何解决这个问题的.这可以有多种方式，也许他们有解决方法，也许他们会意识到这是使用全局临时表的错误方法，也许他们会告诉您迷路.无论哪种方式，这是最好的方法:您已将问题提出到适当的级别.
祝你好运.
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;
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
/// EDIT
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 (
"
+ "DEAL_ID NUMBER,
"
+ "id NUMBER,
"
+ "karpardaz VARCHAR2(350),
"
+ "GOOD_TYPE_GROUP VARCHAR2(250 BYTE),
"
+ "GOOD_CODE VARCHAR2(50 BYTE),
"
+ "GOOD_TITLE VARCHAR2(250 BYTE),
"
+ "COUNT NUMBER,
"
+ "FACTOR_COUNT NUMBER,
"
+ "GHABZ_COUNT NUMBER,
"
+ "DEAL_NO VARCHAR2(50 BYTE),
"
+ "DEAL_DATE DATE,
"
+ "REQUEST_NO VARCHAR2(50 BYTE),
"
+ "REQUEST_DATE DATE,
"
+ "REQUEST_CLIENT VARCHAR2(250 BYTE),
"
+ "STATUS VARCHAR2(250 BYTE),
"
+ "TYPE VARCHAR2(250 BYTE),
"
+ "GEN_SECURITY_DATA_ID NUMBER(10),
"
+ "MTDREPORT_ID NUMBER
"
+ ")
"
+ "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 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.
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.
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>
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.
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:
- Drop and re-create a global temporary table
- Populate temporary table
- Select from temporary table into PL/SQL array
- Insert into actual table using bulk insert from PL/SQL array
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"
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:
- Ignore the
ORA-14452error, proceed into production and then use the "but you told me to" defence when it all goes horribly wrong. This is the weakest play. - 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.
- Speak to your boss. Tell them you're running into the
ORA-14452error, 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.
Good luck.
相关文章