刪除或修改暫存表格方法 (GLOBAL TEMPORARY TABLE)

新增欄位

TRUNCATE TABLE TPMPTABLE;
ALTER TABLE TPMPTABLE ADD (F1 VARCHAR2(6));

刪除表格TPMPTABLE

DROP TABLE TPMPTABLE;

刪除表格若出現以下錯誤

錯誤報告 - SQL 錯誤: ORA-14452: 嘗試建立, 更改或捨棄已在使用中之暫時表格的索引 14452. 00000 - "attempt to create, alter or drop an index on temporary table already in use" *Cause: An attempt was made to create, alter or drop an index on temporary table which is already in use. *Action: All the sessions using the session-specific temporary table have to truncate table and all the transactions using transaction specific temporary table have to end their transactions.

---

1.確定使用此表格的SID

SELECT SID, SERIAL# FROM V$SESSION V 
WHERE SID IN (SELECT SID FROM V$LOCK  L, DBA_OBJECTS O 
WHERE L.ID1 = O.OBJECT_ID AND O.OBJECT_NAME =UPPER('TPMPTABLE'));

2.刪除SESSION，成功後再做一次刪除表格動作(刪除SESSION的ORACLE帳號權限必須夠大)

ALTER SYSTEM KILL SESSION 'SID, SERIAL#';