CREATE GLOBAL TEMPORARY TABLE ...
[ON COMMIT PRESERVE ROWS ]
CREATE GLOBAL TEMPORARY TABLE ...
[ON COMMIT DELETE ROWS ]

临时表中的可选项：

ON COMMIT PRESERVE ROWS ，事务提交自后会oracle会清除临时表中的数据

ON COMMIT PRESERVE ROWS ，事务提交之后会保留数据，知道session结束。

其中发现一点，如果临时表是以ON COMMIT PRESERVE ROWS创建，如果想对表进行ddl操作，会提示如下错误：

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.

提示中指出，所有用到这个临时表的session需要抽取表中数据，或者所有的特定事务完成才可以做drop。

摘自toad knowledge xpert 帮助：

For a session-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session

即truncate table tablename后

下面摘除完整的说明：

You can create indexes on temporary tables same as you would on permanent tables.

For a session-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the session. This binding goes away at the end of the session or by issuing a TRUNCATE of the table in the session.

For a transaction-specific temporary table, a session gets bound to the temporary table with the first insert in the table in the transaction. The binding goes away at the end of the transaction.

DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table. Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.

Temporary segments are de-allocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables. If you rollback a transaction, the data you entered is lost, although the table definition persists.

You cannot create a table that is simultaneously both transaction- and session-specific. A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.

The data in a temporary table is, by definition, temporary, so backup and recovery of a temporary table's data is not available in the event of a system failure. To prepare for such a failure, you should develop alternative methods for preserving temporary table data.

ITPub博客

临时表

最新文章