Temporary Tables

 

    

     In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.(on commit delete rows or on commit preserve rows)

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data. DML locks are not acquired on the data of the temporary tables. The LOCK statement has no effect on a temporary table, because each session has its own private data.
(存储在temp table中的数据是session级别隔离的，每个session只能看到自己的数据，Locks在temp table是无效的，原因就是因为我们的session隔离，不需要控制并发)

A TRUNCATE statement issued on a session-specific temporary table truncates data in its own session. It does not truncate the data of other sessions that are using the same table.
(truncate只截断自己session中存储的临时表中的数据，不会影响到其他的session)

DML statements on temporary tables do not generate redo logs for the data changes. However, undo logs for the data and redo logs for the undo logs are generated. Data from the temporary table is automatically dropped in the case of session termination, either when the user logs off or when the session terminates abnormally such as during a session or instance failure.
(在临时表上的DML操作对于数据改变是不会生成redo logs,然而，Undo是会的生成的，而我们都知道undo是受redo保护的，因此redo logs for undo logs自然还是会生成)

You can create indexes for temporary tables using the CREATE INDEX statement. Indexes created on temporary tables are also temporary, and the data in the index has the same session or transaction scope as the data in the temporary table.
(我们可以在临时表上建立index,而建立的index也是临时的，而且也是session private的)

You can create views that access both temporary and permanent tables. You can also create triggers on temporary tables.
(我们还可以将view建立在temporary和permanent的table上)

The Export and Import utilities can export and import the definition of a temporary table. However, no data rows are exported even if you use the ROWS clause. Similarly, you can replicate the definition of a temporary table, but you cannot replicate its data.

Temporary Segment Allocation
------------------------------------------
Temporary tables use temporary segments. 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, then the table appears to be empty.
(临时表是创建在临时段上，但是和permanent table不同的是，临时表和索引在创建时，不会自动的分配临时段，只有在第一次insert的时候才会分配临时段)

You can perform. DDL statements (ALTER TABLE, DROP TABLE, CREATE INDEX, and so on) on a temporary table only when no session is currently bound to it. A session gets bound to a temporary table when an INSERT is performed on it. The session gets unbound by a TRUNCATE, at session termination, or by doing a COMMIT or ABORT for a transaction-specific temporary table.
(只有在所有session都unbound的情况下，我们才能在临时表上perform  DDL)

Temporary segments are deallocated at the end of the transaction for transaction-specific temporary tables and at the end of the session for session-specific temporary tables.
(临时段在事务提交based 'on commit delete rows'，或者session退出based 'on commit preserve rows'的情况下才会deallocated)

Parent and Child Transactions
------------------------------------------
Transaction-specific temporary tables are accessible by user transactions and their child transactions. However, a given transaction-specific temporary table cannot be used concurrently by two transactions in the same session, although it can be used by transactions in different sessions.

If a user transaction does an INSERT into the temporary table, then none of its child transactions can use the temporary table afterward.

If a child transaction does an INSERT into the temporary table, then at the end of the child transaction, the data associated with the temporary table goes away. After that, either the user transaction or any other child transaction can access the temporary table.

 

本文出处：http://blog.youkuaiyun.com/aicon/archive/2009/02/03/3860304.aspx