關於 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@]