关于 oracle 临时表 ORA-14452

最新推荐文章于 2024-06-05 17:20:53 发布

转载最新推荐文章于 2024-06-05 17:20:53 发布 · 665 阅读

文章标签：

#oracle #table #session #insert #integer #delete

转载-数据库专栏收录该内容

2 篇文章

订阅专栏

本文介绍了Oracle中临时表的两种类型——事务级与会话级，并通过实例演示了如何创建、使用及清理这两种类型的临时表。重点说明了在尝试删除已使用的临时表时可能会遇到的ORA-14452错误及其解决办法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

关于 oracle 临时表 ORA-14452

--总结：不管事务级还是会话级的临时表，都需要所有会话解除绑定，才能drop，解除绑定的办法就是清空每个会话的数据
--清空数据的办法：事务级别的临时表：commit/truncate table;会话级的临时表：truncate table ；
--注意，事务级临时表：不能用delete所有数据然后commit这种方式清空，必须用truncate table，才能解除该会话同临时表的绑定
--事务级：on commit delete rows;
--会话级：on commit preserve rows;
--ORA-14452: attempt to create, alter or drop an index on temporary table already in use避免此错误就是解除所有数据绑定，清空每个使用的数据。
--测试会话级临时表
sqlplus
sql> set sqlprompt "SESSION 1"
SESSION 1> CREATE GLOBAL TEMPORARY TABLE TMP01 (int_id integer) ON COMMIT PRESERVE ROWS;

Table created.

SESSION 1> insert into tmp01 values (11);

1 row created.

SESSION 1> select * from tmp01;

INT_ID
----------
11

SESSION 1> commit;

Commit complete.

SESSION 1> select * from tmp01;

INT_ID
----------
11
SESSION 2> insert into tmp01 values(22);

1 row created.

SESSION 2> select * from tmp01;

INT_ID
----------
22

SESSION 1> truncate table tmp01;

Table truncated.

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

SQL> truncate table tmp01;

Table truncated.

SESSION 1> drop table tmp01;

Table dropped.

--测试事务临时表
SESSION 1> CREATE GLOBAL TEMPORARY TABLE TMP01 (int_id integer) ON COMMIT delete rows;

Table created.

SESSION 1> insert into tmp01 values (11);

1 row created.

SESSION 1> select * from tmp01;

INT_ID
----------
11
SESSION 2> insert into tmp01 values(22);

1 row created.

SESSION 2> select * from tmp01;

INT_ID
----------
22

SESSION 1> commit;

Commit complete.

SESSION 1> select * from tmp01;

no rows selected

SESSION 1> select * from tmp01;

no rows selected

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

SESSION 2> drop table tmp01;

Table dropped.