关于 oracle 临时表 ORA-14452

 

关于 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.