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.[@more@]