转：

Oracle 临时表创建及删除

Oracle临时表

临时表分为两种

会话级别（ON COMMIT PRESERVE ROWS;）

CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT PRESERVE ROWS;

事务级别（ON COMMIT DELETE ROWS;）

CREATE GLOBAL TEMPORARY <TABLE_NAME> ( <column specification> )
ON COMMIT DELETE ROWS;

创建临时表时，可以使用TYPE快捷创建，操作如下

CREATE GLOBAL TEMPORARY TABLE TEMP_TABLE OF TYPE_NAME ON COMMIT DELETE ROWS;

删除临时表

TRUNCATE TABLE <TABLE_NAME>;
DROP TABLE <TABLE_NAME>;

删除临时表时，偶尔会出现ORA-14452的错误，意思是有资源还在占用临时表，无法删除。需要找到临时表的会话，手动杀掉。

SELECT sid, serial#
FROM v$session
WHERE sid = (SELECT sid FROM v$lock
WHERE id1 = (SELECT object_id  FROM dba_objects
WHERE object_name = UPPER('临时表名')));

ALTER system kill session 'sid,serial#';

标签: 数据库